+ Reply to Thread
Results 1 to 11 of 11

Formula help - a "today's date" formula?

  1. #1
    Registered User
    Join Date
    04-18-2018
    Location
    NY
    MS-Off Ver
    office 365
    Posts
    9

    Formula help - a "today's date" formula?

    Hello

    I need help with some formulas for 3 columns on my spreadsheet. I need a formula that changes red when the date inside it is 3 years older than today’s date, but stays green when not. It also needs to change red when there is no date. When I say today, I mean when I open it on the February 23rd that’s “today's” date, or if I open it on March 12th, that’s today's date, etc.

    I am pretty new to Excel and see all the really awesome things you can do with it, it’s just sometimes I don’t know how to do it. I tried looking up a formula using today() and IF but I am not sure how to put the formula(s) together. I also need one that changes at the one year mark, but I figure I can just change the number in the formula for the one above?

    The person who created it applied conditional formatting, but I think you have to keep changing dates, some are listed two or three times, or are partial, or not needed anymore, etc.... It is just a mess. I figure there has to be a better way with a few formulas, rather than having almost 30+ different (no exaggeration) conditional formats. Any help would be great, and if you need clarification on anything just let me know. THANK YOU!

    Excel question picture.docx

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula help - a "today's date" formula?

    go to conditional formatting, click on new rule, go to use a formula to determine which cells to format, then use this formula =DATEDIF(A2,TODAY(),"y")>=3 then click on format and format either the font or the fill or what you want if the condition is met.
    (assuming your date is in A2, adjust it to your cell location).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula help - a "today's date" formula?

    Hello and welcome to the forum. You would be better off attaching a sample Excel workbook than a picture of it inside of a Word document.

    That being said, you can use two Conditional Formatting formulas (one for each color) to give you the result that you are looking for.

    Let's say that the cells that you want to apply the Conditional Formatting to are A2:A1000 and that you want to fill the cells red when the date is at least 3 years old (or blank) and green when the date is less than 3 years old.

    Highlight A2:A1000 > Conditional Formatting > New Rule > Use a formula
    =A2<=EDATE(TODAY(),36)
    Format: Fill red > OK > OK

    Highlight A2:A1000 > Conditional Formatting > New Rule > Use a formula
    =A2>EDATE(TODAY(),36)
    Format: Fill green > OK > OK

  4. #4
    Registered User
    Join Date
    04-18-2018
    Location
    NY
    MS-Off Ver
    office 365
    Posts
    9

    Re: Formula help - a "today's date" formula?

    I'm sorry, I'm not sure how to attach a sample. Hopefully this is it.
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula help - a "today's date" formula?

    ok, so here is what I did.
    removed your conditional formatting already applied, to do this go to conditional formatting, click clear rules and remove from entire sheet.
    now if you want a base as green set up the green (or whatever color you want) to the cells you want, then do the conditional formatting for the sheet in the areas you want.
    so conditional formatting for cell B8 I used is =DATEDIF(B8,TODAY(),"y")>=3 and applied as red fill.
    CF I used for E8 is =OR(E8="",E8>1)
    CF for F8 is =DATEDIF(F8,TODAY(),"y")>=1
    CF for G8 is the same =DATEDIF(G8,TODAY(),"y")>=1

    here is your sheet with the changes.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-18-2018
    Location
    NY
    MS-Off Ver
    office 365
    Posts
    9

    Re: Formula help - a "today's date" formula?

    OMG...perfect! Thank you so much for your help!!.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula help - a "today's date" formula?

    You're welcome! And thank you for the rep!

  8. #8
    Registered User
    Join Date
    04-18-2018
    Location
    NY
    MS-Off Ver
    office 365
    Posts
    9

    Re: Formula help - a "today's date" formula?

    One more question. Can i also apply this to google sheets as well?

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula help - a "today's date" formula?

    Sorry, I have no experience using google sheets so I don't know. Perhaps someone else will stop by who does.

  10. #10
    Registered User
    Join Date
    04-18-2018
    Location
    NY
    MS-Off Ver
    office 365
    Posts
    9

    Re: Formula help - a "today's date" formula?

    No problem, thank you for all your help!

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula help - a "today's date" formula?

    sure, no problem. there is a section on the main forum for other platforms and they do have google info there, maybe that will help you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array formula help: "Who is working today?"
    By Eiden in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-06-2017, 07:48 PM
  2. If Sheet("Entry").range("P3") has not today date then run macro
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2016, 12:13 PM
  3. [SOLVED] Formula or function to populate a range of cells based on "TODAY()"?
    By Bonzopookie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2014, 05:59 PM
  4. Replies: 4
    Last Post: 09-20-2014, 07:10 AM
  5. [SOLVED] Having trouble with "=Today()" formula in macro
    By Jiptastic in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-04-2013, 12:36 PM
  6. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  7. [SOLVED] Formula: How to write.."if A2 is blank then b2=today's date?
    By wheresleo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1