+ Reply to Thread
Results 1 to 3 of 3

Excel formula for changing cell colour when date inputted is getting closer.

  1. 07-17-2024, 03:49 PM #1
    maisiegm
    maisiegm is offline
    Registered User
    Join Date
    07-16-2024
    Location
    Burnley
    MS-Off Ver
    Office 365
    Posts
    1

    Exclamation Excel formula for changing cell colour when date inputted is getting closer.

    Hi everyone i really need help with this asap, i have a spreadsheet with a lot of different dates on for when a specific certificate renewal needs to be booked in. I need it to auto generate a colour as the date is getting closer. for example cell changes to red when less than 3 months away, yellow for less than 6 months away and green for more than 6 months away. I have all different dates that are inputted so the formula needs to be adaptable for all dates and change colour when date is approaching between a certain time period. Any help would be greatly appreciated its for my work and i don't know what todo?
    Reply With QuoteRegister To Reply
  2. 07-17-2024, 08:17 PM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Excel formula for changing cell colour when date inputted is getting closer.

    Welcome to the forum. It's always best to upload a sample file so that whatever solution we give is more suited for your scenario. Manually color what you're hoping to see so that we know we understood you correctly.

    For this, maybe select the range you want to apply to (say FROM A2:A100)
    go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
    =A2<EDATE(TODAY(),6)
    Format yellow fill.

    The other 2 are similar:
    =A2<EDATE(TODAY(),3)
    =A2>=EDATE(TODAY(),6)

    The condition for yellow also fulfills for red and hence I did it first to let the red supersede it.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 07-18-2024, 07:14 AM #3
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Excel formula for changing cell colour when date inputted is getting closer.

    With dates in cell A2 and below,

    CF formula for ≥ 6 mth: =DATEDIF(A2,TODAY(),"m")>=6
    CF formula for 3-6 mth: =AND(DATEDIF(A2,TODAY(),"m")>=3,DATEDIF(A2,TODAY(),"m")<6)
    CF formula for < 3 mth: =DATEDIF(A2,TODAY(),"m")<3
    CF formula for past due: =ISERROR(DATEDIF(A2,TODAY(),"m"))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Changing Colour of Cell Based on a Date of Birth
    By spencer695 in forum Excel General
    Replies: 5
    Last Post: 04-09-2019, 02:53 PM
  2. [SOLVED] Changing the colour of a cell in direct relation to the date within Excel 2007
    By PTSO in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-28-2014, 08:56 AM
  3. Combining codes and changing cell colour using date.
    By kawaitang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2013, 08:56 AM
  4. [SOLVED] Inserting new macro - Changing Cell Colour - Formula to update date
    By Rhiannon25 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-11-2012, 11:11 PM
  5. Changing a cell colour when the date is over a year old
    By davezb in forum Excel General
    Replies: 3
    Last Post: 07-14-2012, 02:36 PM
  6. Help changing colour of cell depending on date entered
    By dazlia in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-07-2011, 10:59 AM
  7. a cell changing colour base on date comparion
    By mambo84 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2010, 02:59 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1