+ Reply to Thread
Results 1 to 7 of 7

Google Sheets - Formula or conditional formatting help - a "today's date" formula?

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

    Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    HI

    I posted this in the Excel formula section and it was solved quickly. The problem is that i did not know I cannot use those formulas and conditional formatting in google sheets...or i am not putting it in right. I see in google sheets there are date options, but non of them seem to fit my needs.

    What i need is:
    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?

    Any help would be greatly appreciated!!

    Capture.PNG

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,687

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    Choose "Custom Formula" in conditional format.

    Use following formula:
    =datedif(cell reference, Today(),"y")>2

    0.JPG
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

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

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    Awesome, thank you so much!!

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,687

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    You are welcome and thanks for rep

    If this solved your query, please mark the thread as solved by using thread tool found at top of your initial post.

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

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    I think it stops highlighting red at exactly 3 years, i need it to stay red.

    Forum snip 2.PNGForum snip 3.PNG

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,687

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    Just adjust # of years you use to get desired result. Or change "y" to either month ("m") or days ("d") to get finer grain control.

    As well, when you have multiple condition. You need to make sure that either: 1) condition does not over lap, 2) stop if true is applied on first check.

    If you want Pink flag up to and including 3 full year... (ex. 4/24/2015 or before will be pink if checked on 4/24/2018).
    =datedif(M2, Today(), "y")>2

    And for Green only if greater than 3 full year (ex. 4/25/2015 and later will be green if checked on 4/24/2018).
    =datedif(M2, Today(), "y")<3

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

    Re: Google Sheets - Formula or conditional formatting help - a "today's date" formula?

    Of course there was a really simple answer, lol. Great, thank you again!

+ 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] Formula help - a "today's date" formula?
    By Nolarobinson in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-19-2018, 02:10 PM
  2. [SOLVED] Google Sheets: Need help with "Is Not Equal To" formula over two sheets.
    By Peaceful Jim in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 16
    Last Post: 03-06-2018, 08:30 PM
  3. Replies: 4
    Last Post: 09-20-2014, 07:10 AM
  4. Using "AND," "OR," and "TODAY()" in conditional formatting
    By KArnoldColumbia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2013, 01:17 PM
  5. [SOLVED] Conditional formatting - formula if cell does NOT contain "," or "@"
    By Armitage2k in forum Excel General
    Replies: 12
    Last Post: 12-02-2012, 06:23 AM
  6. Conditional Formatting - Replace date with "YES" or "NO"
    By christinie in forum Excel General
    Replies: 3
    Last Post: 11-01-2012, 04:10 PM
  7. Conditional Formatting "Text" Month from =TODAY() or =NOW()
    By SolomonDolphin in forum Excel General
    Replies: 6
    Last Post: 02-27-2009, 06:37 AM

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