Comments on: How to conditionally format dates and time in Excel with formulas and inbuilt rules

See how to apply Excel conditional formatting to dates. Learn how to use formulas to highlight weekends and holidays, format cells when a value is changed to a date, shade upcoming dates and delays, conditionally format dates based on the current date, and more. Continue reading →

Comments page 22. Total comments: 1245

  1. Hello: Hoping that someone can assist. I have a spreadsheet that has column E containing 'approval dates' and column F containing 'expiration dates'. I want to use conditional formatting to column F to turn green if the date in the cell is 30 days or more from expiration date; yellow if it is within 30 days of the expiration date and red if it is 30 days or less from the expiration date. Thank you.

  2. Hi there,
    wonder if you could assist with a problem I'm having?

    I have a spread sheet which contains a date range i.e. 01/01/16 - 10/01/16. Each date in a separate cell.
    On another sheet I have a calendar. I am wondering if there is any way I can have a formula so that the day in the calendar is automatically filled with a number/coulour when that date range has been entered in previous sheet or else the day remains blank.
    Many thanks

  3. Hi,

    Thank you for the great assistance you all are providing in this blog.

    My question is, I have a column in excel that contains (varying dates in certain cells and blank cells with dates yet to be entered).

    I am hoping to create a formula for the entire column that initiates a green fill in the cell that contains a date (30 days after the date range specified). I hope this makes sense.

    Your advice would be greatly appreciated.

    Kind Regards, Paul

  4. Hi,

    I want to conditional format date & time (in same cell dd/mm/yy hh:mm) with respect to another cell(in reference cell dd/mm/yy hh:mm), please help

  5. Hi.
    I'm looking to generate an automatic RAG (Red, Amber, Green)status for a spread sheet based on today's date and future dates calculated on today's date.
    In other words, if an activity is on time, it will format as green, if it is behind time by, say, 2 weeks, it will be amber and anything beyond, say, 4 weeks will format as red.
    The icons seem to be the way to do this, however, I am struggling with the correct formula.
    Can you help please?
    Thanks

  6. sorry should read B5 B6 etc

  7. i have successfully formatted dates in relation to today as i wanted, with your help thanks.
    however it is formatting when no date is inputted in the cell. how do i correct this?
    =TODAY()-$B$4>=15 (highlighted RED) this is the format used.

      1. Works great thanks.
        another i can do this for a single row, however when i try to apply to the rest of the page this is still referencing the original cell $B$4.
        how to I make it reference the corresponding row C4 D4 etc.
        i have tried removing the $ but this makes the formula invalid.

        help

  8. Hi, could you please write for me the formula for calculation the duration between two dates with a condition like

    10 Aug 2016 but it is required to consider 21 Oct 2016

    Minus

    21 Nov 2016 but it is required to consider 20 Nov 2016

    Required date difference is (21 Oct 2016 - 20 Nov 2016)

  9. I have a spreadsheet with various dates - two of which are:
    Start Date Actual completion date

    I want to be able to add a colour to the Actual Completion date field if the date entered is 30/60/90 days after the start date (ie 30days=green,60days=amber, 90days=Red) but I can't work out how to do it.
    Can you help please

    1. Hi Karen,

      You can create three rules with the following formulas:
      1. For the red color:
      =$B1-$A1 >= 90
      2. For the amber color:
      =$B2-$A2 >= 30
      3. For the green color:
      =$B2-$A2 < 30
      Make sure that the rules are in the following order: red, amber, green.
      The Start date values are in A1:A100, the Actual completion date values are in B1:B100.

  10. Hello,

    I have created a live-spreadsheet for Orders with 'Date Required' and 'ETA' columns in them.

    I would like the dates in the 'ETA' to be highlighted if they are greater than the dates stated in the 'Date Required' column.

    I can get this to work on individual Rows through Conditional Formatting but cannot repeat the process on a large scale through-out the entire sheet.

    Please let me know if you can help.

  11. Hi
    I need to highlight time in Green if within the 30 minute window of the stipulated shift timings and Red everything beyond that. We have 3 shifts with 9am, 10am & 11am.
    Can you help, I am struggling to do that.
    Thanks & Regards

      1. Hi Team,
        This is how it is and I need to highlight the one's which are compliant within the agreed time in that shift

        In Timing
        S.#Name Shift (AM)Monday Compliant Tuesday Compliant
        1Aditi 10:00 9:00 Yes 9:15 Yes
        2Anshuja 10:00 10:00 Yes 9:45 Yes
        3Archana 11:00 11:00 Yes 11:00 Yes
        4Arunjit 11:00 10:25 Yes 10:45 Yes

  12. I have a date in column e and then in column f I have added 3 years to when I need an update to renew what is in column e. Now I would like to know how I can get column a to turn red when it is 60 days before column f is due.

  13. Hi
    Cell A2 is the Invoice Submitted date (1 Sep 16) and B2 is the payment release date ( 10 Oct 16). Now I want the entire row to be highlighted in yellow color 10 days before the payment clearence date is near
    And second I want the entire row to be highlighted in red color if payment is not done on time

    1. Hi satya,

      You can create two rules with the following formulas:
      1. For the red color:
      =$B2-$A2>=10
      2. For the yellow color:
      =$B2-$A2<10
      Make sure that the rules are in the following order: red, yellow.

  14. Hello, i'm creating the list of documents in my storage. In column E i have "Retention Start Date, column F is "Retention Period. and column G is "Retention End Date". What i would like to do is to get the cells in the "Retention End Date" to turn red when it reaches a end date in real time. For example, if the start date is 10/12/16, and retention period is 7 years, the next column would be 10/12/23, and it should only be highlighted in red when the end date is equal to the computers date. Is this possible? How can i do this?

    1. Hi Hamizan,

      You can create a rule with the following formula:
      =$G2=DATE(YEAR($E2) + $F2, MONTH($E2), DAY($E2))

  15. I have to log complaints in a database and am looking for a better way to follow-up if there has been no response provided. I am to receive a response within 48 hours of receipt of the complaint. I would like to use the conditional formatting to highlight the date received column for the complaints I have not received a response from within this time-frame.
    So far this the formula I have come up with =IF(IF(Y2="",IF((TODAY()-$A2>2),"followup",""),"")="followup",TRUE,FALSE).

    After using this formatting, in the "Date Received" column it didn't highlight the date of the complaint that has the "Date Resolved" cell blank/gone beyond the 2 day timeframe. I don't know exactly what else I need to input to make it work, but I am determined to figure out why.

    Thanks I hope this make since!

  16. Hi,
    I have a spreadsheet used for tracking training dates for employees and supervisors. I need a formula for highlighting the supervisors recurrent training date a year from their initial training for just 1 column. I used the formula: =TODAY()-$L4>=365 and formatted it to turn red when it is over a year, which worked great; however, not everyone on the spreadsheet is a supervisor so those cells under that column are either blank or "N/A". How do I include the blank and N/A cells so they are not highlighted?

  17. Hi,
    i've been working on this spreadsheet which has a column for the "date of recieving comments", and another for "date of resubmission".Assuming we hav 13 days time between the 2 dates, is it possible to include a column that alerts about the deadline.
    can u suggest a method to do it?
    thanku

  18. Hi,

    Please help, =IF(DAY(F6)=1) is not working in my sheet. i want to change the dates 1-7 = Color 1, 8-15 = Color 2 and so on...

    Thanks

    1. Hi ripudaman,

      You can create different rules to change the color, for example:
      1. For 1-7 days:
      =DAY($B1)<=7
      2. For 8-15 days:
      =AND(DAY($B1)>7, DAY($B1)<=15)

  19. Hi

    Can you please tell me how can I find a number of particular value from a column...

    For e.g.: suppose 'Type' column has five type with 1000 records I want to find a number of records belong to particular type in a different excel sheet cell.

    Thanks.

  20. Great work. Thanks a lot.

  21. WORKDAY will work perfectly for what I need to do, but I want to include weekends in my calculations. What formula do I use instead of WORKDAY?

    1. Hi Aly,

      If you are referring to the formula in comment 252, you can just use the TODAY() function:

      Golden rule: =AND($A2>=TODAY()-2, $A2<TODAY())

  22. Please help me of my worksheet I need formula that will notify me the start date and end date. Thank you Svetlana.

  23. I currently use an Excel spreadsheet to track patient appointments and due dates for quarterly/annual exams, etc. I would like to be able to enter the date of the last exam and use conditional formatting to program the cells to change color as the due date for their next appointment becomes more and more urgent. If a patient was seen 12/1/16, would need to be seen again after one year. I would like the cell to turn yellow after 6 months, orange after 9 months, and red when overdue (after one year). Can you help?

    1. Hi Calvin,

      You can create three rules with the following formulas:
      1. For the red color:
      =TODAY() >=DATE(YEAR($A1) + 1, MONTH($A1), DAY($A1))
      2. For the orange color:
      =TODAY() >=DATE(YEAR($A1), MONTH($A1) + 9, DAY($A1))
      2. For the yellow color:
      =TODAY() >=DATE(YEAR($A1), MONTH($A1) + 6, DAY($A1))
      Make sure that the rules are in the following order: red, orange, yellow.
      The "patient was seen" values are in Column A.

  24. I am trying to create a spreadsheet using hire dates, to alert me to when an employee's anniversary is coming up that month! What formula would I need to use in conditional Formatting in order to achieve this goal?

  25. I have 2 columns. One is the start date, the other is the end date. I would like the third column to show "Active" or "Expired" depending on the "end date" in column 2.
    Thank you!!

    1. Hello Christine,

      You can use a formula similar to this, where B2 is the end date:
      =IF(B2<TODAY(), "Expired", "Active")

      Please note, this task does not require making a conditional formatting rule. You just enter the formula in the top cell, and then copy it down the column as usual.

  26. I currently have an excel with dates in columns I thru AA. Dates do not begin until row 4. Those cells have dates of the last time training was completed. It is all annual requirements or semi-annual requirements. I am trying to display dates more than one year old as red; dates between 1 year and 9 months as yellow, and dates 9 months to today as green. Are there any formulas that would assist in that? Thank you.

    1. Hi Dan ,

      You can create three rules with the following formulas:
      1. For the red color:
      =TODAY() >=DATE(YEAR($A1) + 1, MONTH($A1), DAY($A1))
      2. For the yellow color:
      =TODAY() >=DATE(YEAR($A1), MONTH($A1) + 9, DAY($A1))
      3. For the green color:
      =TODAY() >= $A1
      Make sure that the rules are in the following order: red, yellow, green.

  27. Hi,

    I want to display COMPLETE in green, IN PROGRESS in Yellow, PENDING in grey color based on date comparison between two columns and today's date.
    I tried conditional formatting option -> Use a formula to determine which cells to format
    and the formula used was =IF(D2<TODAY(),"COMPLETE","IN PROGRESS"). But it is not working.
    Can you suggest ...

  28. Hi, in column F I have due dates, and in row 1 I have dates as well for time going by. How do I do conditional formatting so that the following for example happens:

    Cell E2 has a date of 8th of November, and AK1 also has a date of 8th of November. I want to have AK2 to then colour orange. And I would want to apply this to the whole table.

    Thanks,

  29. I have start date in A2 cell and End date in B2 cell, looking for conditional date differance in C2 as if End date is not defined then differance should be C2=Today-A2, if End date is defined then C2=B2-A2
    Please Help

  30. I am trying to apply conditional formatting to times related to swimming which are in minutes, seconds and milliseconds. Currently the times are formatted in a Custom Format of mm:ss.00. I've tried the formula option without success. Basically I'm looking to be able to make a cell turn yellow when a swimmer's time is within a second of a current time standard (i.e., greater than 0 seconds but less than or equal to 1 second). How do I go about achieving this conditional formatting? Thanks

  31. Good Afternoon,

    I have a company training matrix which i would like to format. The majority of our training expires in 3 years.
    The date we insert on the matrix is when the training will expire eg. 28/8/19.
    Could you please advise how to format this that 1 year prior to expiry date cell will change from white to another colour and 6 months prior to expiry cell will change colour again?

    Many Thanks,
    Felicity

    1. Hi Felicity,

      You can create two rules with the following formulas:
      1. For the red color:
      =TODAY() >= DATE(YEAR($A1), MONTH($A1) - 6, DAY($A1))
      2. For the yellow color:
      =TODAY() >= DATE(YEAR($A1) - 1, MONTH($A1), DAY($A1))
      Make sure that the rules are in the following order: red, yellow.

  32. Hello Mam,

    I need a help in condition formatting. and amount should be in blue colour if cell value exceed 30 and within 61.

  33. Hi! Could you please help me with this simple question. I have a column for Invoice Due Date. I selected Conditional Formatting, Highlight Cell rules, Less Than and then typed the formulae TODAY(). It immediately highlighted the entire column. When I put in a date less than today the highlighting remained and when I put in a date more than today it became white again. But I don't want the entire range highlighted in the first place. Want it highlighted only when the condition is met. Am I doing something wrong? Thank you so much for your help

  34. I am working in a traveling agency. so I would like to calculate time by giving some condition, means in one column we type starting time and next column we type ending time and in the next column I give formula to count the total time taken to a trip. But here some time I should give some conditions means if the time is less than 12:00 then it should be round off as 12:00, if it is greater than 12:00 then it should be remain same. Is there is any formula I can use for this in excel.In the normal number format I am using the formula but in the time format I am not able to use the formula.
    Please help me in this.

  35. i was wandering if their was a way that you could highlight time such as 2:58 if so how would you do so

  36. I have been trying feverishly to create a conditional format formula for an approaching deadline, as well as a deadline which has been met and/or expired. I have utilized many different formulas, but all to no avail, as I cannot accomplish what I have set out to do with the formulas:

    1. Format an alert, in the form of a red-colored cell for the deadline that has expired (and/or is past due)- when the date arrives (e.g., say the deadline is 9/3/2016.. on that day, I want the cell to turn red),

    2. Format an alert in the form of an golden-colored cell for 48 hours prior to the deadline (I want the cell to turn gold when the 48 hours before the deadline date arrives, using only weekdays),

    3. Format the deadline in the form of adding 9 days to the date entered into another cell, & the second deadline alert for 48 hours (weekdays only) before the final deadline (which is the 9 days after the initial set date)

    I came across this formula: =IF(A3>> http://www.journalofaccountancy.com/issues/2005/mar/programexceltoalertyoutoadeadline.html

    Can anyone please help me? This is really and truly, driving me batty man! I have been working on this project for 4 days now!

    -Crystal

    1. OH MY GOODNESS CRYSTAL! I need this too! Any chance you were able to figure it out since you posted online in Sept?

      1. Hi Crystal and Aly,

        I do not fully understand condition 3. As for the first 2 conditions, you can create the conditional formatting rules with the following formulas, where A2 is the top-most deadline cell:

        1. Red: =$A2<=TODAY()

        2. Golden: =AND($A2>=WORKDAY(TODAY(), -2), $A2<TODAY())

        Important note! For the rules to work correctly, the golden rule should be the 1st in the list of rules.

  37. Hello,

    In Example 1. Highlight dates equal to, greater than or less than today.

    What do you have listed in your "Applies to" section?

    I have done exactly what your example says, but it's either not highlighting anything, or highlighting a seemingly random set of ranges.

    Thanks,
    Chris

  38. Hi, Mam i want Ms excel VB formula for for date which is equal or greater then date for 3 days more. i mean when date is less then today and more 3 days.
    so that i will high light for 3 days that the reminder date is passed then after 3 days that column will not highlighted.kindly reply on my email.

  39. i have to collect rents from different shops every month.in one column i have added the last date to collect the rent and in OK column i add ok if i collect the rent.i want a conditional formatting that if due date exceeds my date column goes red and if i receive the rent and write OK in the other column my date cell goes green

  40. i have to collect rents from different shops every month...in one column i have added the last date to collect the rent and in OK column i add ok if i collect the rent.....i want a conditional formatting that if due date exceeds my date column goes red and if i receive the rent and write OK in the other column my date cell goes green

  41. hello. I am trying to update a number 2.5 every 30 days starting on the 1st of the month. I would also like that number to change colors from green 0-44 to yellow at 45-59 and red at 70+

    so if my number starts out at 10 in 30 days starting on the 1st the number updates to 12.5 and then in 30 more days 15 etc.

  42. Mam, I need to highlight the date which will exceed 900 days or 30 days from the mentioned date. Kindly help me.

  43. Hi,

    Please help me if you can.

    A1 contains a date
    A2 contains number of days (Colour 1)
    A3 contains number of days (Colour 2)
    A4 contains a date
    Column B contains date (Jan 1 to Dec 31)

    I am looking to highlight "A2" number of days in red and "A3" number of days in blue starting on the date "A1". This alternating red and green pattern must continue until the date "A4"

  44. Hi,

    I am producing a sheet at work, and need some help. I have two columns with dates. Column A is date request received, column B is date request dealt with. I want to conditionally format column B cells to take account of the following. If date if column B is greater than 10 working days after the date in column A to highlight the cell red.

    It is intended to highlight those dates where we have missed our 10 working day target.

    I have tried various formulas to no avail.

    Thanks in advance

  45. I am trying to build a training database and simply want the Date of Training to be compared to TODAY. If it's over 365 days then condition format the text to red.

    Can anyone help with the formulae to add as a rule. I can adjust for each cell manually.

  46. Hi,

    I have a query with Date.
    If a Checkbox in B2 is ticked then C2 gets today's date else "Pending".

    Note: Checkbox has been added with the help of Developer tab from the Ribbon.
    I have linked them with cell F2 also to have True or False value.

  47. Re my post - it's solved -

    Conditional format is

    Cell Value = Cell of that the date you need

    i.e.

    Cell value = $B$22 < this is where I have the date 1/1/2016 < I will then be making this cell a dynamic year so that whenever I change the year in B1 the calendars will automatically populate the conditional format based on year :)

  48. Hello,

    If I have two columns of dates (column I and column J) and I want to highlight in red cells in column J only if the date in column J is past the date of the corresponding cell in column I. Other than that I want it to be white.

    Please help.

    Thanks!

  49. Ok - have spent several hours now trying to conditional formatting of a date with no luck perhaps you can help :)

    I'm using a dynamic calendar - you'll probably know where I'm going with this but posting for others who don't know.

    The year input

    Cell - B2=year # - manually input a year i.e. 2016
    Cell - B3=Month # - manually input a month i.e. 1
    Cell - B4=1st day of month in the first row (i.e.. Friday is Jan 1st)i.e. 6

    The Calendar Grid

    Top - the month and year
    Cell - B5 =month(c5)
    Cell - C5 =EDATE($B$3,0) < cell is merged C5 to H5 to give is uniformed look

    The Days of the Week
    B6 =B8 < this is copied over to column H ie.=b8,c8,d8 ending up at H8

    The calendar numbers

    Starting Row
    -Cell-B7: =IF(WEEKDAY(C5)=1,C5,"")
    -Cell-C7: =IF(B7"",B7+1,IF(WEEKDAY(C5)=2,C5,"")) < this formula copies over to H7(changing column heading as you go)

    2nd row
    -Cell-B8: =H8+1< this formula copies over to H7(changing column heading as you go)
    3rd row - same as 2nd
    4th row - same as 2nd

    5th row
    -Cell-B11: =IF(H10<EOMONTH(C5,0),H10+1,"")
    -Cell-Cll: =IF(B11<EOMONTH(C5,0),B11+1,"") < this formula copies over to H7(changing column heading as you go)

    Row 6 (last row of calendar Grid)
    -Cell-B12: =IF(H11<EOMONTH(C5,0),H11+1,"")
    -Cell-C12: =IF(B12<EOMONTH(C5,0),B12+1,"") Cells D to H are empty

    I need to conditional format holidays i.e. Jan 1st, Dec 25th or a range of dates(1 week vacation), etc but nothing I've tried thus far works - countif, match, if, and, etc. I think the issue is that I'm using a dynamic formula to create a date (the variable) and the holiday date is a date item fixed (i.e. Jan 1, 2016 is day 42370 in excel).

    Any help would be appreciated.

    Thanks

  50. Hello,

    If I have two columns of dates (column A and column B) and I want to highlight column B only if the date in column A falls within the same week of column B.

    Please help.

    Thanks!

    1. Hi Audra,

      You can create a conditional formatting rule with this formula (where 2 is the topmost row with dates):
      =WEEKNUM($A2)=WEEKNUM($B2)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)