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

  1. Hi,
    Need Help

    I want to know the expire date of the certificate is the following formula for conditional formatting is correct?

    =AK4>EDATE(TODAY(),3)

    1. Pradnya:
      I think it would work better if you put the value for TODAY in another cell and referenced that cell. For example: =AK4>EDATE(AK3,3) would work better. Where the cell AK3 holds the formula TODAY().

  2. Hi team,

    I have a query. How do I go about conditional formatting (highlight a whole row) based on the last working day of every month in a data set - loan amortization schedule. For example, if the date is the last working day in a particular month, the whole row is highlighted a particular background colour.

    Thank you for your assistance.

  3. Hello,
    Thank you for your blog.
    I would like to format a column of dates to highlight any date < or + to TODAY, if there is a particular text present in another column of the same row.

    Said another way.
    I would like the date in Column H to highlight when < or + to TODAY, only if Column D has an "a" or "na" text in it.

    I've tried numerous different formulas without success.
    Any insight would be greatly appreciated!!!

    Thank you.

  4. Please can someone help?

    I am wanting to add some formulas to my spreadsheet.

    I have column L (which has a date inserted into it) and column M which will also have a date inserted manually once work is completed, BUT I want Column M to turn red if it has been 7 days after the date inserted into column L, only if a date hasn't been entered into column M. Any solutions?

  5. Hi,

    I have a column with tenant names, a column with move in dates, and a column with Lease expiration dates.

    Without adding an additional column, is it possible to have the column with the move date turn yellow 2 months before the 3 years anniversary without the actual move in date changing? I also would like this to happen every 3 years.

    1. Becky:
      Yes, you can do this and some other date related things, too.
      To highlight the move date:
      Select the cell(s) containing the signing date(s)
      Select Conditional Formatting from the ribbon or wherever it is in your version of Excel
      Choose New Rule based on formula
      enter =TODAY()+60 in the field
      Choose the formatting of your choice then OK out and you're finished.
      If you want to see in another cell what the status of your tenant's dates are, enter this in another cell where A42 contains the signing date of the lease:
      =IF(A42<TODAY()+60,"2 Mos","OK")
      If you want to see the day and date their lease expires, enter this in another cell:
      =EDATE(A42,36) where "36" is the number of months from the signing date.
      When you enter all of this in the various cells, your lease sheet should display a lot of info.

  6. Hi,

    I have a column with tenant names, a column with move in dates, and a column with Lease expiration dates.

    Without adding an additional column, is it possible to have the column with the move date turn yellow 2 months before the 3 years anniversary without the actual move in date changing? I also would like this to happen every 3 years.

    Is this possible?

    Is this possible?

  7. im having an issue where i want the date in column B to go red when the date entered has passed but will only change color when column A contains a specific word.

  8. I am needing to indicate whether a guests arrival date occurred within 7 days of their booking date. The data resides on two separate sheets (one sheet for the guest name and booking date and another worksheet for the guest name and arrival date.A yes/no answer or the actual # of days between the two dates would be amazing.

    1. Nikki:
      Sheet 1 Cell A1 "Name" Heading
      Sheet 1 Cell B1 "BookDate" Heading
      Sheet 1 Cell A2 is the guest's name
      Sheet 1 Cell B2 is the booking date
      Sheet 2 Cell A1 "Name" Heading
      Sheet 2 Cell B1 "Arrival Date" Heading
      Sheet 2 Cell A2 is the guest's name
      Sheet 2 Cell B2 is the arrival date
      Sheet 2 Cell C2 =DATEDIF(Sheet1!B2,B2,"D")
      Sheet 2 Cell D2 =IF(C2<=7,"Yes","No")

  9. I have a spreadsheet with 2 columns of dates. Column A and column G. I need to conditionally format cells in column G that have dates within a certain number of days from the dates in column A. For example, if the date in column G is 0-2 days older than the date in column A, the cells need to be GREEN. If the dates in column G are between 2-4 days older than the dates in column A, they need to be YELLOW. If the dates in column G are 5 days or older than those in column A, they need to be RED.

    Can this be done?

  10. Hello, I think something is wrong with my excel. I used the exact format for the conditional formatting of a row based on a cell. Using the formula =$B2=43182 where all the columns are selected. I get highlights, but they don't match the date I wanted them for, which in this case is 3/23/2018. Some 3/23/2018 rows will be highlighted, others won't, and some wrong rows will be highlighted with different dates.

    I am trying to do a of multiple weeks of similar data. I want to highlight the different weeks so I can track when someone doesn't appear in latter weeks.

  11. I am trying to write an excel formula so a cell will turn red if due date is past due but if another cell is filled in before due date for it not to turn red.Is this possible? I will be transfering the formula to smartsheets to use in there too.

  12. This will work:
    Enter this into cell H5
    =IF(MONTH(I5)=MONTH(TODAY()),"Current Month","")
    Then in the Conditional Formatting window enter
    in first field: Cell Value is
    in second field choose is Equal to
    then in the third field enter ="Current Month".
    Then format the cell in the manner/pattern you want to display.

  13. I have a spreadsheet that I'm creating for a friend. They hire out various things and take multiple payments. I would like to highlight the cells net to the date when its the current month EG cell I5 has the date but i would like H5 to change colour when its in the current month the date is entered (DD,MM,YYYY). I'm pretty certain this is possible but every time i try i seem to get 1,1,1900 and it conditional formats to this date

    1. This will work.
      Enter this into H5
      =IF(MONTH(I5)=MONTH(TODAY()),"Current Month","")
      Then in Conditional Formatting window enter
      Cell is equal to
      Then in second field choose Is Equal to
      Then in third field Enter ="Current Month"
      Then choose formatting of your choice

      1. Thank you Doug that works great. Sorry for late reply

  14. Hello Svetlana,

    on your Example 3. Highlight upcoming dates and delays the duration there is based on total number of days.
    How about the duration is within a months, for example duration = 6 months. Should I need to convert the 6 months into days?

  15. Hello Svetlana,

    Thank you for sharing your knowledge. I have a question and not sure if you can help me. I am managing a project and have a very simple Gantt chart created to track the project. Is there a way to add a daily tracker, of the current date in excel, that moves horizontally along the Gantt chart to see where we are at today (progress bar)? If you send me your email address, I can send you the spreadsheet or a screenshot. Thank you very much for any help.

  16. Hello,
    I have a simple excel sheet that lists 2 columns - the first has names of instructors with the second listing the date when training was last completed. I want the oldest date out of all those listed to show up in red and all others to show up green. The dates are in dd/mm/yyyy format. What's the easiest way to implement that conditional formatting?
    Thanks!

  17. I have a spreadsheet where dates in once cell (next order due) are calculated from a formula based on a date in a different cell (date order received). However, I want next order due dates that I have entered manually to appear in red so I know the date was not calculated from a formula. This way, I remember to update them manually when a new order is received. I pray that you can help me. :)

  18. I am looking to create a rule that highlights cells in a single column. These cell would be date equal to or less than today's date. I need to make this continual for everyday updates.

    Thanks in advance.

  19. Hello!

    I hope someone can help me. I have excel file consisting our product inventory. I hope conditional formatting can help me manage our product expiry. For example, at June 17, 2018 our X product will expire, I want the cell to highlight 3 or 4 months before the said expiry date. This will help me lot. Thanks.

  20. Hi Irina;
    Assume I created all 31 columns and conditional format to highlight weekend and holidays.
    I wonder if or how can I lock those weekend and holidays columns ( what I mean is no entries allowed below that columns).
    Thanks

  21. Hi;
    I hope someone can help me on an attendance sheet which there are 31 columns for 31 days in a month (ex: january).
    I want to know if columns that contain " Sat" and " Sun" or " Holiday" will be locked and no entries allowed.

    Which formula or conditional format or VBA to use that command. Thanks

  22. Thanks you for details formatting concept explained.

  23. =AND(TODAY()>=$E$2,TODAY()30 days from target AND <the target date. (dates in serial format in my case).

  24. Dear all,

    I would like to highlight the dates of the contracts that will end up in the future 90 days... But I don't know how to do it. I have tried so many things but nothing seems to work.

    Can you please help me to fix that?

    Best

  25. I am trying to change a color code in column A based on the date in column E. Currently I am only able to apply the change of color in column E itself but I would need to change the different colors in column A based on a booking system so I basically like to reset all cells in column A with the color blue and orange to color green every single day to show that they are available to be booked again. Is there any way to do this?? Any thoughts much appreciated!

  26. I am using the following formula:
    =IF(L15="","",IF(ISNA(L15),NA(),L15-TODAY()&" Days Remaining"))
    to determine how many days remaining until the next service of plant & equipment (working on a 6 monthly cycle)
    And then when applying conditional formatting I am using:
    When Cell Value is greater than =30&"Days Remaining" turn green & When Cell Value is less than =30&"Days Remaining" turn red.
    But when the cell is showing 1 - 9 days remaining it turns to green rather than red. How do I fix this?

  27. Hi anyone please help me how calculate expiry date.

    For Ex :- Training start date 11-Jan-17 and training end Date 15-Jan-17.

    What will be training expiry Date after 3 years in formula.

  28. My spreadsheet has a last revision date column with a date and a "Date of next review" column next to it with the formula of =S114+365. I would like to have the "Date of next..." to be highlighted if it is within 30-60 days yellow and less than 30 days in the future as red

    formula =AND($A2-TODAY()>=30, $A2-TODAY()=1, $A2-TODAY()<30)

    isn't cooperating, any help is much appreciated

  29. Hello & thanks in advance for the assist!
    I'm using conditional formatting for color coding cells with dates (formatted 01-JAN-17). In column A, I've called out/highlighted in red dates older than 1-JAN-17. In column B, I've called out/highlighted in green dates older than 1-JAN-16.

    Now, I would now like to call out/highlight in yellow also in column B, a combination of column A & B to call out dates in column A newer than 01-JAN-17 and in column B where dates are older than 01-JAN-16.
    Thanks!

  30. I have a lot bills to pay on a monthly basis. I want to create an excel spreadsheet that reminds me when to submit my payments. For example, my electric bill is due January 24, 2018. I want to make a column with green fill color stating that I am at least 10 days from the due date. When it is 5 days from the due date, I want the fill color to turn yellow.
    Can you please help?
    Thanks.

    Rino

  31. Status Budget Current Phase Target prod
    12/12/2017 100% 11/10/2017 12/25/2017
    12/10/2017 125% 10/09/2017 12/30/2017
    10/10/2017 50% 08/01/2017 08/09/2017

    Hi,
    Above data is my report example, i need to identify the over budget, one month old status, Passed current phase, Upcoming current phase, passed Target prod and Upcoming Target prod all together in one cell as a result.

    Please help me to code in this, i have tried in the below format but the last code i'm getting volatile as a error. I dono how to fix this.

    =
    IF($C$2>=100%,"Budget Overage.","",
    IF($B$2-TODAY()>30,"Status Report More Than 30 Days.","",
    IF($D$2=TODAY(),"Upcoming Target Prod Rel Date."
    IF($E$2TODAY(),"Passed Target Prod Rel Date.",))))

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  32. I'm trying to compare the difference in scheduled and actual times. So a person is scheduled to report to work at 9:00a but the clock in at 9:05. I need a column to show Late and On time when those two times are compared.

    1. Hello,

      You can use a formula like this:

      =IF(A1>TIME(9,0,0),"Late","On time")

      where cell A1 is “9:05”

      Hope it will help you.

  33. Hi

    I have a number of dates in a spreadsheet of when licenses for forklifts were issued in my company. We refresh the guys every 3 years.

    How would i go about making these cells turn red when the 3 years is up from the original issue date?

    Each cell contains the date when they completed the test

    1. Hello, Gerard,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  34. Hi,

    Similar to the query above, I have a spreadsheet with Names in the rows, organized based on the day they were added to the spreadsheet and dates in the columns. I want the Day 1 column to be automatically highlighted on the day the name is added, and then automatically have the highlighted cells shift left as it becomes Day 2, 4, 7, etc. after that name has been added to the spreadsheet. I then want to create conditional formatting so that once an X has been placed in a cell (marking that that name has been called on that day), the highlight disappears as that task has been completed. Does that make sense?

  35. Hi :)
    I have a spread sheet showing columns with start dates, end dates, then data (i.e. deposits required, pre delivery payments required and final invoices required) all running off the start and end dates.
    Im trying to high light the cell for deposits required once the start date is a certain number of dates away. Can i achieve this ? i don't want the date cell to be highlighted i want the deposit required cell to be high lighted (or if both can be high lighted that would be even better)

    looking forward to your response IL get some serious brownie points if we can do this :)

  36. Hi, I have dates in column C and in column D is the date 29 days from the dates in column C. I want the cells in column D to change color when it is 25 days from the day in column C. Thanks for any help.

  37. Hello,
    This is my situation. I've made an employee schedule with conditional formating so that certain things are specific colors. The dates are across the top row. The employee names in the first column on the left. One of the items I formatted is that when I cancel an employee's shift I have formatted the cells to highlight red when I type a "C" in them. I also want the date of the cancel or "C" that was entered into the schedule to automatically appear in the column on the far right. And since an employee could potentially be cancelled more than one time per month I need only the most recent date to appear in the column on the right. I also want the information from the column on the right to carry over to the next month which is the next sheet in the same workbook. I have not been able to figure out how to write a formula to solve this issue. Any guidance would be greatly appreciated.
    Sincerely,
    Samantha

  38. If the date in H7 is before the date in G7, I want the font color to be green. If the date in H7 is after the date in G7, I want the font color to be red. How do I do that?

    1. Hello, Nicholas,

      you need to use the most basic formulas that will compare dates in conditional formatting, like the one below:
      =H7this article to learn more about conditional formatting.

  39. Hello,

    I wanted to automatically have the date and time in column B generate whenever an entry was made in the corresponding row of column A. I was able to accomplish that, but now the tricky part is getting cells A through D for that row to change yellow if the date/time generated in B is between the the current date at 6am and 630pm, and then change blue if the date in B is between the current business date and 5:59am on the next day. I was able to get the blue shade condition to work, but not the yellow for the morning entries. Would you be able to assist with a formula?

  40. Hi is there a way I can highlight dates in a column that cannot exceed another date in another column? For example, I have a spreadsheet where one column consists of dates where patients received their services and another column expressing their discharge date. I want to look for any discrepancies where the service dates exceed the discharge date.

    Thank you.

    1. Hi, Kelly,

      you need to create a simple conditionally formatted rule where one date (let's say, in A1) is bigger than the other (for example, A1>A2).
      Please take a look at this point of the article to learn the principle. You can adjust the formula, simply change "TODAY()" to the cell that contains other date for comparison.

      Hope this helps!

  41. Hi, I have created a spreadsheet that contains a column with a list of review dates for patients. Can you help me find the best formula to use so that the dates that are overdue will show in red, the dates that are due within two weeks will show in yellow and the dates that are more than 2 weeks away will show as green. There is another column next to this one that shows wether a patient has an appointment booked (it has the appointment date in it), is there a formula I could use to ensure that the dates in this column are ahead of the actual review date?
    Thanks very much

    1. Hi, Leigh,

      it's very difficult to come up with any particular formula since we don't know how your data is stored.

      Please take a closer look at the "Example 3. Highlight upcoming dates and delays" part of the article above and you'll see the formulas that may be of great help.
      Otherwise please send us a small workbook with your sample data to support@ablebits.com. I kindly ask you to shorten the table to 10-20 rows.
      Our technical specialist will take a look at your task and try to help.

  42. I have a list of 20 names (A2 thru A21) who are participating in a 6 week program. Column C is adm/week 1. Column D is week 2(7days from date in Column C). Column E is week 3 (7days from date in Column D), and so on. The date of each of the 6 weeks is populated in subsequent columns/rows using the formula =DATE(YEAR(C3), MONTH(C3), DAY(C3)+7). I am trying to figure out how use a formula so all participants in week 1 will have a yellow cell(column C), week 2 a light red cell(column D), week 3 a light green cell, week 4 a light purple cell, week 5 a light blue cell, and week 6 a light tan cell. Can you help me?

  43. Hello,

    Can you please help me to get appropriate excel formula for the following case?
    I have the work sheet that contain different information and I wanted to get the warning when specfic name is incerted & the dead line is passed.
    eg. Column A= the cell where the date is recorded
    Column B= The list of names
    Column C= Dead line dates
    So, I need to get the alarm (red color) when the two conditions are fulffliled. i.e The name is inserted among the list of d/t names and the deadline is passed.

    Thank you in advance.

  44. kindly advise to get the formula for expiration after 30 days and after 5 days using conditional formatting.

    Ex.

    Date created: Sept 1 then it will expire after 30days or it will expire within 5 days.

  45. how can I formulate this one: example

    Column 1 = August 01, 2017 10:08 and
    column 2 = August 01, 2017 16:00

    Time should not be exceeded to 4 hours, if its exceeded comment should be delayed, if not exceeded its not delayed

    Thank you

  46. In your first example of using the built-in date conditional formatting I had already done this, but I would like to have it format the entire row instead of just the cell. I have three conditions set up.
    1. Is anything "This month"
    2. Is anything "Next month:
    3. Is anything before today.
    1 overrides 3. My formatting works well, but I wish this set up would highlight the entire row based on these conditions as opposed to just the cell. Anything further than the next month will not need to be highlighted, but this will be an ongoing spreadsheet with changing dates as information is updated.

    1. Hello, Adam,

      to format the entire row you need to open Conditional Formatting Rule Manager (Home tab > Conditional Formatting > Manage Rules), select the rule and enter the whole row to the Apply to field (e.g. B:B, or A1:C1 supposing you have 3 columns).

      If you don't want the other rules to be applied when the first condition is met, make sure to select the Stop If True check box next to those rules in the same Conditional Formatting Rule Manager.

      Hope this helps.

      1. This works for columns, but not rows. If I go to manage rules and select a bunch of rows (say 2-11) it still only highlights the cell within that date range, not the entire row. I have columns A-M and the only column where the date range matters to me is column I. My conditional formatting works to highlight dates in a range in that column, but I would like it to do something like highlight all of row 3 if the date in column I falls in my range, instead of just cell I3.

        Thanks for your help.

  47. If at all possible I would love to send in a snapshot of my spreadsheet so that you may easily identify my issue and help to resolve in a timely manner. Could you please provide me with a forwarding email address.

    Thanks In advance,

    Lemar Harper
    (915)929-4934

  48. I am attempting to format blank cells so that when a date is entered into column D, column E will populate with the expiry date. I'm confident that my formula is correct but once it is applied those cells with no date in column D are being populated with a date in column E. See below for clarification

    Formula in active cell E4: =DATE(YEAR(D4)+1,MONTH(D4),DAY(D4))

    Rank Trained Expires
    8/15/2016 8/15/2017
    9/30/2016 9/30/2017
    9/10/2016 9/10/2017
    11/14/2016 11/14/2017
    11/16/2016 11/16/2017
    8/31/2016 8/31/2017
    8/31/2016 8/31/2017
    8/2/2016 8/2/2017
    9/2/2016 9/2/2017
    8/29/2017 8/29/2018
    12/31/1900
    12/31/1900
    12/31/1900
    12/31/1900
    12/31/1900

  49. Hi
    I have a list of various deadline dates. I would like to add a column for "activity start date" which would be fixed at 2 weeks prior to the deadline date - so the formula would be the same but the actual dates will all be different

    Is there such a formula as "activity start date" = "deadline date" - 2 weeks

    deadline start
    31.6.2017 needs formula to calculate and input "17.6.2017"

    Thanks
    Paul

  50. I have a spreadsheet where I have a sent date and to be completed date. If the Sent date is between 7-13 days before the to be completed date it needs to be yellow and no color if it's 1-6 days before the to be completed date. If the Sent date is more than 14 days after the date to be completed date it should turn red. Now if the project was completed in the time frame and there is a Y in the completed column the cell needs to go back to join color. Someone please help me!!!

    1. Hello, Heather,

      since you have 4 conditions, you will need to create 4 rules for conditional formatting using AND function. Also you need to know how to subtract dates in Excel.

      So, if your Sent date is in A2 and To be completed date is in B2, your first rule will be like this:
      =AND((B2-A2)>=7,(B2-A2)<=13)
      Choose the filling, make sure to apply it to =$A:$B, and check the Stop if true box.

      Please use this example and the links above to learn more about all this functions and create the rest of the rules.
      Hope this helps!

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