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 23. Total comments: 1245

  1. 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())

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

  3. 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.

  4. 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?

  5. 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.

  6. 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.

  7. 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 ...

  8. 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,

  9. 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

  10. 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

  11. 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.

  12. Hello Mam,

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

  13. 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

  14. 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.

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

  16. 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.

  17. 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

  18. 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.

  19. 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

  20. 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

  21. 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.

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

  23. 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"

  24. 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

  25. 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.

  26. 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.

  27. 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 :)

  28. 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!

  29. 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

  30. 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)

  31. Dear Madam,

    My office grants 3% increment on salary every year July. I want to program in such a way that on July 1st the increment and salary should get changed to new proportion.

    Please guide me

    Regards
    R Dharma

  32. Please can you help me out on this.. i have many policies but the due date is my problem i can't remember all of them so plz help that i get the due at at least 10 day before.

    Thanks

  33. Please can you help me out on this..I have a conditioning formatting with colour on employees date of assumption of duty. the colours helps me to know those who are up for confirmation depending on when each assumed duty. wat formula can help me stop the colours from appearing if the date of confirmation is due and past. Please any solution

  34. Hello,

    I'm maintaining an attendance table in the following format.

    01 Wed IN 01 Wed OUT
    08:27 17:16
    08:36 14:31
    08:38 17:07
    08:32 17:05
    08:47 17:05
    08:31 17:04
    08:16 17:13
    08:31 17:13
    08:29 17:05
    08:40 17:06
    08:32 17:02
    08:52 17:05
    08:36 17:08
    08:30 18:37

    I want to apply conditional formatting for "IN" column and "OUT" column. If IN time > 8:45, the font in that cell should be RED. If OUT time < 17:00, the font in that cell should be RED.

    The data in IN and OUT columns is a result of LEFT(cell, 5) and RIGHT(cell,5) from another column that records every IN and OUT entry of the staff.

    I have tried conditional formatting options but I'm obviously doing something wrong. Tried formatting the cells as Time, Custom, etc.

    Appreciate your help.

    Thanks.
    Sunny

  35. Hi I'm trying to produce a spreadsheet that high-lights a whole line in grey when I add a date into one cell on the line to show that the info no longer needs any action. The cell is empty until I add the date after which I want the line to go grey.
    Thanks

  36. If I have an inspection date and it comes due again in 6 months,
    How do I create the formula to reflect this and have the cell turn red with 30 days before the due date?
    Can someone provide Me a formula? I've tried several and still get a 'False' for the answer.

  37. I have a Weekly calendar that start on Last Wednesday the previous month; the cell 2 has dates with formulas and change each month and is merge the cell 3 has 10 box example B3,c3,d3,e3,f3,g3,h3,i3,j3,k3 with the information. I need to transfer to the daily calendar and the daily calendar start on the first of the month and the weekly calendar change every month.

    Do you know what formula I can use? Thank you for you help.

  38. Can anyone help me with this? I have an excel file with 2 worksheets(A and B). On worksheet A, i have a weekly guide for activities. On worksheet B, i have the activities and the percentage as which are completed (0% to 100% completion). On A, i want a certain cell to be colored, say gray, if the certain cell in B between 0% and 99% completed for the week, and another color if it will be 100% completed for the week or next. Thank you so much.

  39. Hi, how do I format dates of the month to say this week, next week of this month?

    1. Or this month i mean.. Thanks!

  40. Does anyone know if it is possible to create a conditional formatting rule based on time?
    I am creating an action tracker type tool and would like to have the cells formatted to reflect if is it coming due or overdue. I know it can be done for Dates - but in this instance it is going to be based on time.
    e.g. Assigned at 1000, due at 1230 - once is past due time flag red, coming due change orange etc.

  41. is it possible to start the colouring of the conditional formatting of the cell from the beggining

  42. Hello,

    I am hoping you could help me with a little conditional formatting problem I am having.

    I have a sheet full of dates particular employees have completed training on.

    I am trying to get the cells to turn green if the date is less than two years from today and red when the date entered into the sheet is older than 2 years (730 days)from today.

    eg. If the date in the cell reads 01/06/2015 it would be green and if the date in the cell reads 01/06/2013 it would be red.

    NOTE: Example above assumes today's date is 01/06/2016

  43. Hi, I need your help with color coding cells within a column that contains a vlookup formula to bring in that date, I need to color code those dates that fall within a range a certain color, 0 to 6 months, 6 months to 11 months and 11 months and older. Since some of the cells have #N/A from no data found from the vlookup, these would be left non-colored. Is this something you can help me with.

    Thank you,
    Jacqueline

  44. Hi
    How to I create a table that when the date from the drop down list is approaching the next cell will change colour. eg, next month will make the cell turn red as it is a closer date and 3 months time will be orange and any dates further away will be green

  45. Hello,

    I am trying to figure out a way of showing if an item has been ordered more than once in a 6 month period.

    I have the list of product codes in one column and the date they were ordered in an adjacent column. Is there a way of highlighting repeated product numbers within a 6 month period?

    Thank you

  46. Hello!

    I have a column of different dates that appear in this format (12-Aug-15) and I would like to format these dates based on their age.

    For example, I would like the cell to be green if the date in the cell is less than 180 days old, orange if the date is greater than 180 days but less than 270 days, and red if the cell date is 365 days or more.

    Any help would be greatly appreciated!

  47. Hello I have a spreadsheet where I have columns C,F,I,L,O, and R with dates. I used the this month and next month rule but want to add a rule for 60 to 90 days and 91 to 120 days. I used the following formula but it did not seem to work: In 30 to 15 days, inclusive: =AND($A2-TODAY()>=15, $A2-TODAY()<=30). I switched out the 15 for 60 and the 30 for 90. I am not sure how the $A2 applies and that is where I am getting lost. I assume it is the start of the spreadsheet date but have played out with this with no results. Can you please assist.

    Thank you

  48. For my work's tracking sheet, we highlight the cells yellow for attempts with the date listed as mm/dd/yy and no highlight when we have made contact with the date of contact as mm/dd/yyyy. I am trying to get the cells to automatically highlight when entered as mm/dd/yy. I tried the =CELL("format",$A2)="D4" formula and it did not work. Any suggestions?

  49. Hi
    I have this scenario where in column A I have the date (04/25/2016) and in column B I have the time (12:46 PM)
    I am trying to find a formula where I can change the date and time when the time mentioned in the column B is 3:00 Pm or above and it should reflect the next day in the date column with 7:00 AM as the time in Column B.

    Example : column A - Column B
    04/25/2016 - 3:00 PM
    It should change to
    04/26/2017 - 7:00 AM

    Is there any way I could do that ?

  50. in conditional formatting how to highlight dates greater than 10th of every month ?

    1. Hi Suresh,

      You can create a rule with this formula, where A1 is the topmost cell with date.
      =DAY($A1)>10

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 :)