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

  1. Hi friends,

    If am using the some tracker in excel for one year in that i need to differentiate the weeks in the month. for example - jan month 1st week yellow color, 2nd week green color, 3rd week pink color,etc the same will be followed for subsequent months by using conditional formatting option.

    Kindly help on this.

    Thanks in advance!

  2. hello,

    i want to know that if i want to highlight due dates in the column such that the due dates are remain highlighted even after the next month.

  3. Hello. I would like to ask some assistance regarding this matter. I have two columns - the first column contains the date until the certificate is valid & the second column named as STATUS. I would like to turn the "STATUS" column to have values such as "UP-TO-DATE" or "EXPIRED" this is in reference to the date(s) of the first column against the present date.

    Example

    VALIDITY OF CERTIFICATE STATUS
    January 01, 2015

    let's say the date today is April 25, 2017. Looking at this, the certificate is already expired. How am I going to format it so that the STATUS column shows UP-TO-DATE / EXPIRED?

    Thank you for your assistance.

  4. I have a spreadsheet of clients who we ring every 3 months. I have a column with the date of the last call - I'm attempting to format cells where the date is over 3 months ago so we know who needs a call. I'm using the formula - =$C2-TODAY()>90 but the results I'm getting are not what I'd expect e.g. today's date is 14/4/17 but it's highlighted a cell 23/3/2017. Some of the cells don't have dates in them but text such as not active - is this causing my troubles or have I made a mess of the formula

  5. Hi

    I have a date received column (Column A) and column titled notice of complaint issued (Column B). I want the notice of complaint issued cell to highlight red if the date entered is more than than 28 working days after the date received.

    Struggling to get anywhere near working that one out, so any assistance hugely appreciated.

    Thanks
    Rob

    1. Hi, Robert,
      first of all, you need a formula which will count the number of working days between your dates – that would be Excel NETWORKDAYS function (you can read more about it here). The next step is to use this function when creating a formatting rule for your cells:
      =NETWORKDAYS($A2, $B2)>28

      Follow the steps from the article to create a formatting rule using the formula above. Hope it helps!

  6. Hello,
    I have not really found an answer to my problem and I hope you can help me.
    I am comparing 2 days and 14 days difference
    Column B (arrival date): 30/08/2017
    Column A( expected delivery date): 15/08/2017

    If Column A has less than 14 days after the arrival date (Column B) to be highlighted in red, also if the date is even before the arrival Date (Column B) also to be highlighted in red.

    Many thanks in advance

    1. Hello, Dilyana,
      for your first condition, you need to create a formatting rule with a following formula:
      =($B1-$A1)<14
      As for the 2nd one, please, explain in more details the condition you wish the dates to meet.

  7. Hi,
    In my spread sheet I want to highlight an entire row based on the year, i.e. rows with 2016 to be blue, 2017 to be green. The cell will contain a full date - 2016-11-30 - but I don't care about the month or day. The only information I have found seems to be on months and days. Can you help me?

    1. Hi, Elizabeth,
      there are other ways you can play with.

      One of them, is to create a new formatting rule, where you choose Format only cells that contain – specific text - containing – end enter 2016. And make these cells to be filled with blue. Same goes for 2017 – a new rule with a corresponding text value and colour in the rule.

      Another way is to use YEAR function. Let's say you enter dates into A column, and there's 2016-11-30 in A1. You create a new formatting rule with a next function:
      =(YEAR($A1))=2016
      But the same should be done for dates that contain 2017.

      Hope it'll help!

  8. Hi,
    I have a question - i have a sheet with multiple columns, now for example if i put a date in column R then i would like the entire row to be orange, later once i decide to put a date in column S then the same row should turn Green. How do i do this.

    1. Hi, Bosco,
      are you going to put only dates in these columns? If so, you can create two formatting rules that will check if those are blank or not. The formulas for the rules are:
      =$S1<>"" and =$R1<>""
      If your data begins from the second or the third row (and so on), change the number in the formula accordingly.

  9. Hi...
    Lets suppose we have set particular values corresponding to date of the month
    How can i apply a formula i which the value of current date reflect in separate cell

  10. I am trying to format one cell to highlight when the year isn't current. The names listed must complete a task at any date during the current year. After December 31st of each year, the task is overdue. The cell currently has only the year, but it would be nice to have the actually date they completed the task and still have December 31st of the current year as the condition. Thanks for your assistance.

    1. Hello, Sherry,

      if the cell to format is A1 and it contains a full date, use the next formula in conditional formatting:
      =YEAR($A1)<YEAR(TODAY())

      If there's only a year entered into the cell, the formula will be:
      =$A1<YEAR(TODAY())

  11. Hello Juli,

    I am doing an sprint calendar for agile. We have 2 week sprints that start on a Tuesday. I need to highlight the 2 weeks for the current date.

    For example today is March 21 and my sprint is from March 14 to March 25. I like to have those dates highlighted.
    I try to use your DATEIF example but I guess the excel I have does not support that function.
    I Try =AND(TODAY()-B35>=1,TODAY()-O35<=10) but is not giving me the result I need.

  12. Hello,

    I need help, I want to change the dates font color to be black in cell A, if cell B is "done", else keep the due date in red in cell B.

    For exmp:
    Cell A1 =today()
    Cell A4 due date is 4 March 2017 (it is in red if expired)
    Cell B4 is keep counting the over due days
    Cell C4 is where the payment is still in debt. But once the payment is fully paid, cell B turned to "done" automatically. When cell B is "done", the due date in cell A is black again instead of red.

    up to now i am using the format as below

    cell B with: =IF(C4<=0,"selesai ",(A4-A$1)
    For Cell A4 I am using Conditional formatting rules manager

    I am using windows 10

    Thank you

    1. cell B with: =IF(C4<=0,"done",(A4-A$1)

  13. HI .
    I M WORKING AS MAINTENANCE CORDINATOR IN MULTINATIONAL COMPANY. & AS CORDINATOR I HAVE TO PREPARE SCHEDULE FOR THE FIRE FIGHTING EQUIPMENTS BY THE GIVEN DATE & DETAILS OF EQUIPMENTS. BUT I FOUND HARD TO MAKE SCHEDULE WITH EXCEL AS I M FILLING DATES MANUALLY.

    E.G. IF THERE IS ANNUAL CONTRACT FOR ABC CO.

    START DATE-END DATE-TYPE OF EQUIPMENT-SCHEDULED-01ST VISIT - 02ND VISIT -03 VISIT -04 VISIT

    02/02/2017 - 02/02/2018-FIRE ALARM-MONTHLY-?DATE-?DATE-?DATE-?DATE
    02/02/2017 - 02/02/2018-FIRE HYDRANT-QUARTERLY-?DATE-?DATE-?DATE-?DATE

    PLEASE HELP ME IN CREATING THE DATES IF WE PLAN THE MAINTENACE MONTHY OR QUARTERLY

    Any help will be greatly appreciated.

  14. Hi!

    I have a sheet in which I track the status of containers based on their ETA's.
    Currently I have a formula set so that if the dates in the ETA column are in the past (or today), the entire row turns green.
    This allows us to easily identify which containers have already arrived.
    Is there a way to change the cell in the Status column to say "Arrived" based on the ETA column?

    Thanks in advance!

  15. hii.. I have a spreadsheet containing report reciept data.
    we have a deadline of particular date to submit monthly report.
    i want to highlight the dates for reports received after the particular deadline date.

    Can you please guide?

  16. Sir/Ma'am-
    I've tried manipulating the example formula that was used to show how to "conditionally format a cell when a value is changed to a date", however, I don't want the program to highlight a separate row when a date is input, I want it to highlight each specific cell that has a date. I tried using the formula that was indicated as an example just out of curiosity, but it's also highlighting any cell that has "period 1", "period 2", "period 3", "period 4"- and it's also not highlighting some cells that do have dates? Is there any assistance that you could provide? I would truly appreciate it!

  17. Hi!

    I would like to get some idea how to put the conditional formatting in my case;

    If I have many columns with full of dates, and every columns has the required expiration dates. I want to highlight every dates reaching (3 months, 2 months, and 1 month) before expiration so that I can inform the Training Officer for the next schedule for the refresher or retraining course.

    Lets say column A (A1:A50) with different dates and with 2 years validity. I would like to highlight the dates reaching 3 months prior the expiration.

    Hoping to received for your kind response.

  18. Hi, Please help. Is it possible to change font of column F to green if the date in column Q is greater or equal to 01-JAN-17 and have column F in red font of the date in Q is 31DEC16 or less.

    PLease help if its possible

  19. I have a due date and Date Completed column, thanks to this page, I was able to format my due dates with past due and coming up with in a week. Is there a way to format, so that once I add a date to my Completed column, that it will un-fill my due date column, to show as being complete and not past due?

    Or would it be easier to just remove the formatting from a particular cell, once I have completed it?

  20. Hi,

    I have the following conditioning problem

    Column A is a target date, DD/MM/YYYY.

    Column B is the completion date, DD/MM/YYYY

    If a cell in column B is within 5 days of A I would like the cell to turn GREEN.
    5-10 days AMBER
    10+ days RED.

    Is that possible???

  21. I have an interesting problem. I have a spreadsheet that has 3 sheets, one as an overview that the workbook opens to, and 2 with expiration dates.
    The expiration dates are on Emp_Data sheet and Vehicle_Data respectively, I use the formula on the main page to calculate remaining time before expiry:
    =IF(DATEDIF(B2,Emp_Data!B4,"y")=0,"",DATEDIF(B2,Emp_Data!B4,"y")&" years,")
    &IF(DATEDIF(B2,Emp_Data!B4,"ym")=0,"",DATEDIF(B2,Emp_Data!B4,"ym")&" month(s), ")
    &IF(DATEDIF(B2,Emp_Data!B4,"md") = 0,"",DATEDIF(B2,Emp_Data!B4,"md")&" Days")
    This formula removes the year/month/day if it returns a 0. B2 is the current date, and Emp_Data!B4 is the expiration date.
    What I want to do is have conditional formatting for dates that go below 15 days remaining, and another for fields that are above 15 days. Because I use separate sheets, it does not seem to be possible. I have attempted to use condition formatting for numbers, text, and dates for the field, to no avail.
    I have attempted to use a formula to convert the remaining time to days, in a separate field, however, I cannot apply conditional formatting with just one field, as they are all different dates.

    1. You can actually do this. When you are in the conditional formatting screen, you click on the button at the end of the text box and you can change the sheet that it is pulling the information from.

  22. I have a spreadsheet that documents the date when our students respond to questions and I would like to highlight only the first cell of the new date so that when I enter a new date, it will change to same color.

    example:

    student name question date
    john question1 02/15/16 (highlighted)
    Abraham 02/15/16
    elina 02/15/16
    sarah 02/15/16
    lorry 02/15/16
    phil 02/15/16
    phil 02/16/16 (highlighted)

  23. Hello,

    I am trying to conditionally format on dates. With expired dates (which would be over 1 year past current date) turning red, no date being yellow and any date that is current(within 1 year of current date)green.

    Is there a way to format that?

  24. Dear Team,

    I have created excel work book for hotel reservation.There are two sheets named Reservation sheet and I have put dates horizontally & Room numbers vertically into it.In the other sheet (Named Guest booking list) I have put
    Guest Name,ID,arrival date, departure date and room number respectively.I want to fill in reservation sheet cell according to the dates stay when I put the room number into guest booking list.

    Therefore please let me know the If function or relevant method to make this easy.

    Thank you.

  25. This was super, super helpful. Thanks so much for putting together this guide!

  26. =AND(M2>=TODAY(), M2<TODAY()+3) - whats wrong with this formula

  27. Hi,

    I have an excel file in which I am trying to change the cell color(red, yellow, or green) based on the date in the cell minus 0 days (red), 5 days (yellow), and 10 days green. Is that possible? THis is an award tracker and I want to the cell to change color based on proximity to the due date.

  28. I would like to create a formula to find duplicate company names, as well as find those that occur quarterly (ex: In Oct,Nov, and Dec because current month is January).

  29. I have a date in column b that i want highlighted if it is 61 days older than the date in column a

  30. Hello,
    I am working on a spreadsheet where I am tracking IP addresses that show up in our IDS logs. Column B contains the IP address, Column C contains the country code for the IP address, and Column D contains the date that the IP address was reported. I want to highlight the IP address's if it has already be entered into the spreadsheet. I did this with this formula in a conditional format: =COUNTIF($B$2:$B$99,$B2)>1

    I want to take it one step further. I only want to highlight the IP address in Column B if the date of the previous match is greater than 7 days from today. I can't figure out how to get this additional condition. Any help would be really appreciated!

    Jon

  31. Hi, I have a column with different dates (ex: Expected Dates of Arrival) that I need to change colors:
    yellow - 60 days before arrival
    orange - 30 days before arrival
    red - 14 days before arrival

  32. how can i highlight greater dates from today dates

  33. I have a simple spreadsheet that I input dates when a customer is billed. Can I apply conditional formatting to highlight the latest date entered? In other words, say this one customer was billed on 12/30/16 (which is highlighted because it is the latest date) and then he is billed on 1/15/17. I would like the 1/15/17 date to then be highlighted, and the 12/30/16 to become unhighlighted. Is this possible?

  34. I need formula help on the below date related conditional formatting:

    i have leave start date in e5 & end date in f5 and i have allocated 365 individual columns from column i5 for individual dates.

    i want the cells to be highlighted in different color once i have input the Leave Start date & End Date.

    How to do it?
    Thanks,

  35. I have a worksheet to track workorders. I have a column that gives Start Date as "1/1/17 6:00 AM" or "M/D/YY TIME"

    I have seen a few comments on Time, I would like the cell to "fill" if the time is before 7:00 AM or after 5:00 PM.

    Is there a way to make this happen?

    Thank you for your "Time" ;)

  36. I am trying to use conditional formatting to highlight a cell if another cell has a date that is after 1/1/17 and before 12/31/17

  37. I have a yearly calendar set up days and months and all I want to do is change just the year. There has to be a faster way than hitting each individual spot and backspace 1 spot, type new number and then hit enter. Is there a formula that I haven't been able to find or a shortcut to do the whole thing at once? I can send it via email if needs be to show what I am working with. Any help will be greatly appreciated.

  38. hi,

    thanks alot for this useful tutorial, and i have a question :

    i am trying to use those 2 conditional formatting formulas :

    =AND(E$2:K$2=TODAY()+1,HOUR(NOW())>2)

    and this formula :

    =AND(E$2:K$2=TODAY(),HOUR(NOW())<=2)

    where the range E$2:K$2 is a table head and contains a sequence of current week dates

    i want to highlight the column in the table which has tomorrow's date on the table head cell only if the current real time is after 3:00 AM, And if not, i want to highligt the column which has today's date (not tomorrow's) ?????

    note : the range that each of the 2 conditional formatting formulas applies to is $E$4:$K$134 which is my table without the head cuz i don't want to highlight the head.

    i am getting tired trying to do that, can u help me?

    and thanks alot :)

    1. hhhhhhhhhhh finally it worked ! Yes !!

      the right formulas should be :

      =AND(E$2=TODAY()+1,HOUR(NOW())>2)

      =AND(E$2=TODAY(),HOUR(NOW())<=2)

      still really want to thank u, ur tutorial helped me alot, thanks.

  39. Hi team,
    Please, can you help me with this?

    I have a spread sheet which contains a date range i.e. 12/02/17 ( column B ) and 13/04/17 ( Column C ) ( Both in Row 2 ). I would like column B and C to turn yellow if Today's date is in that range only.

    Thank you in advance ! Really appreciated !

    1. try selecting column B & C then make conditional formating which contain the following formula :

      =B$2:C$2=TODAY()

      and choose the wanted formatting

      1. Thanks Jim, but that formula did not work. We are looking to use a formula that will format a time period window. Example we need Cell B2 and C2 to change formatting when todays date is less then C2 and greater than B2.

  40. Hello,

    I have a spreadsheet where all I need to do is for the row colour to change to grey when the date at the beginning of the row is in the past. I can't find the formula anywhere that will make it work!

    Thanks

    1. try selecting the rows then make conditional formating which contain the following formula :

      =$A1:$A9<TODAY()

      where i suppose that A is the column that has the dates (the rows heading), and the rows is 1 to 9

      and choose the wanted formatting (gray)

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

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

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

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

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

  46. sorry should read B5 B6 etc

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

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

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

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

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