1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel formula for annual reminders

Discussion in 'Business Applications' started by Hell2no, Nov 15, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Hell2no

    Hell2no Thread Starter

    Joined:
    Dec 26, 2007
    Messages:
    3
    Hi friends,

    I have not used Excel for ages apart from basic adding formulas etc.

    What I am trying to do is make a set point (date) that turns a box red if one year has passed since the original date entered.
    (not sure if I have explained it very well?)

    ie
    I am making a list of alarms on some machinery & want to show that the alarms have been tested within the past 12 months.
    If I do not get around to testing, I would like the relevant field to change colour, so that a simple check upon opening the spreadsheet will tell me what is overdue on testing.(eventually there will be a lot of different alarms within the spreadsheet)
    I am not sure if it will be easier to make an "issue date" then another field for "review date" or just have the one field that will turn red if today's date passes 365 from the last test date? (is that possible?)

    I am sure it is easy - unfortunately, not for me!

    keep the faith

    Rob
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    You need to apply conditional formatting to the cell you want to change colour.



    Condition is the formula type. Formula is



    =today()>=(yourissuedatecell+365)



    In the formatting, select fill colour to suit and any special borders / font formatting. Hit apply.



    You can change the range affected by the formatting, but you might need to lock the column / row, depending on what you're trying to do.



    No need for a due date field
     
  3. Hell2no

    Hell2no Thread Starter

    Joined:
    Dec 26, 2007
    Messages:
    3
    Thanks Codelexicon,

    I will try it tomorrow.

    Rob
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Do you need an exact date? Taking into account leap year and all that jazz? If so, just use a slight alteration of the formula...

    =DATE(YEAR(YourDate)+1,MONTH(YourDate),DAY(YourDate))>TODAY()

    To include the actual day entered as a date, change from > to >=
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1113064

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice