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 28. Total comments: 1243

  1. Can I apply 2 conditional formats to the one cell such as:

    1.If the cell value changes from $0.00 to a higher amount the text colour will change to red, this conditional formatting I have successfully set.

    2. the text colour of the same cell as above will change to blue if the date changes to a date in the current month in another cell? I have tried a few different formulas, nothing seems to work.

  2. Hi, I have random dates of the year in a column now I want to highlight the higher/last date of each month but not able to find any condition need help.

    Ex-
    8-Feb-15
    9-Feb-15
    10-Feb-15
    20-Apr-15 20-Apr-15
    1-Mar-15
    2-Mar-15
    10-Mar-15 10-Mar-15
    17-Apr-15
    18-Apr-15
    1-May-15
    5-May-15
    6-May-15
    8-May-15
    9-May-15 9-May-15
    10-Jan-15
    11-Jan-15
    24-Jan-15
    25-Jan-15
    26-Jan-15 26-Jan-15
    8-Feb-15
    11-Feb-15
    24-Feb-15
    25-Feb-15 26-Feb-15

  3. Hi. I am trying to create a tracker for all employee certificates. Columns represent each employee and rows define the dates of expiry of certificates. I would like to change the color of the cells when the expiry date is getting closer to today's date. Orange for dates within 15-30 days, red orange for 0-14 days and red for expired dates. I also want to highlight in green the valid dates. I have tried doing it using the formula below but when I test it, the whole row becomes orange and not just the cell. I don't know what I am doing wrong. Please help. Thanks.

    =AND($A$2-TODAY()>15,$A$2-TODAY()<=30)

  4. I use template Pro, I need help this.
    Saturday and Sunday are excluded from workday in Gantt chart pro, but I want to exclude only Sunday. How can I changed this?

  5. how to calculate number of days less than 1 month from column with dates. I couldn't find a formula for this. Is it so simple as it is not here?

    1. Hi Monica,

      If you want to highlight the past dates within 1 month from today's date, you can create a rule with the following formula:
      =TODAY()-$A2<=30

      If you are looking for something different, please clarify.

  6. Also, not sure if this matters but I applied it to rows instead of columns and that seemed to help as well.

  7. SOLVED IT!! This formula works! Posting in case someone else needs it. :-)

    =AND($E2<DATEVALUE("2/7/2015"), $F2<DATEVALUE("2/7/2015"))

  8. Second formula: =AND($E2>DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

  9. It just keeps combinging my formulas....trying two separate comments.

    First formula: =AND(42041<$E2, 42041<$F2)

  10. Not sure what happened with my first comment but it combined my formulas together. The first one I tried was: =AND(42041<$E2, 42041DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

  11. I need help with the following issue. I have a spreadsheet with two columns that contain dates and times in this format: 02/06/2015 12:00AM. All of the cells in column E have values but some of the cells in column F are blank. What I need is for columns A-D to be conditionally formatted when the value of column E AND column F occur before 2/6/2015. I have tried these formulas and they both work on some rows but not on all rows and I cannot figure out why.

    First formula I tried: =AND(42041<$E2, 42041DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

    Both of the above formulas will conditionally format some rows correctly but they both also conditionally format some rows incorrectly and I cannot figure out why Excel can calculate the formula correctly sometimes but not other times. There is no difference in the data types between the rows where the formulas work and the formulas do not work.

    Any help is greatly appreciated.

  12. Just needed some help with conditional formatting. I have two columns both with dates - Column A and B. I want to highlight only the dates in B which Date occur 10 days after the date of Column A

    1. Hi Alwin,

      You can create a rule with the formula =$B2-$A2=10 (row 2 is supposed to be the first row with data.

      1. Thanks for your reply
        while I am using this formula it is showing TRUE/FALSE. Actually I need to highlight that column with a specific color and also need to see the date.

        1. To highlight the column, you need to create a conditional formatting rule with the above formulas. You can find the detailed steps to create a rule in this tutorial.

          1. Hi Svetlana Cheusheva,
            Still I am unable to do this. Please help me to highlight the column B
            Column A Column B
            01-04-2015 11-04-2015
            02-04-2015 17-04-2015
            03-04-2015 23-04-2015
            04-04-2015 09-04-2015
            05-04-2015 15-04-2015
            06-04-2015 21-04-2015
            07-04-2015 27-04-2015
            08-04-2015 13-04-2015
            09-04-2015 19-04-2015
            10-04-2015 25-04-2015
            11-04-2015 01-05-2015
            12-04-2015 17-04-2015
            13-04-2015 23-04-2015
            14-04-2015 29-04-2015
            15-04-2015 05-05-2015
            16-04-2015 21-04-2015

        2. I need to highlight the Column B.

  13. I need to be able to enter a date and then calculate backwards counting only school days. Example: field trip is October 1, 2015. Field trip packet is due 30 school days prior to trip. I need to exclude weekends, holidays and non-student days.

  14. HI,
    I have been trying to figure out a formula that changes the color of the cell to red if the date in the cell is <=10 days away from today's date. i.e if the date in the cell reads 07/12/2015 and I opened the spreed sheet today 07/22/2015 the date on that cell should be highlighted in red.

    1. Hi Manny,

      You can create a rule with the following formula, where A2 is the top-most cell with your dates:
      =TODAY()-$A2>=10

  15. Good day,

    I have a column with dates and I need them to be highlighted if they do not fall in given windows.
    For example:
    Window 1: 1-May-15 till 12-Jun-15
    Window 2: 26-Jun-15 till 31-Jul-15
    Window 3: 14-Aug-15 till 18-Sep-15
    Window 4: 2-Oct-15 till 6-Nov-15
    So I need to know if 07/22/15 does not fall in between any of these windows and have it highlighted if it does not.

    Thank you for your consideration.

  16. Hi,

    I'm trying to highlight a row depending on the difference in 2 dates.
    e.g. if the difference is greater than 1 month then the row needs to go red, if less than a month then the row needs to go green.
    Is there a formula that can do this?

  17. I have 3 columns in excel:
    - Plan Duration Days (e.g.: 15)
    - Start Date (e.g.: 1-June-2015)
    - Available days / week (e.g.: 3)

    Expected Completion Date: 1-July-2015

    Can I calculate expected completion date from the excel formula?

  18. Hi,

    I'm running a transport system, and I need to track where my trucks are at a particular point in time.

    I have an ETA (Expected Time of Arrival) for all the drivers according to their destinations.

    Now, I need a conditional formatting rule to show (in colors) how long a driver spends on his journey at these three points using the commencement date.1. Workshop 2. In-transit 3. Customer Outlet

    Eg. Customer A outlet takes 5 days to arrive. The driver spends two days in the workshop (I need this column to show green), he spends another 3 days in-transit (this should still show green), and he arrives at the customer's location on the sixth day (this will show amber) as he is behind target.

    Regards,

    Reagan

  19. Hello-

    I have read through all of the questions on this page and didn’t see this specific question asked. I would greatly appreciate your assistance!

    I have an excel worksheet- starting in column and row D6 (start date), E6 (End date), and F6 (Frequency). Across the rows at the top we have weeks starting in column and row G4, H4, I4, etc = (July 13, July 20, July 27), etc.

    My first conditional format- to color in the weeks where a communication was sent, worked. That formula was: =AND(G$4$D6) and it applied to: =$G$6:$AM$38

    This worked well for the rows with the frequency of daily or weekly. Where we are struggling with is conditionally formatting rows to fill cells with monthly repetition.

    Currently, the formula I have: =AND(G$4$D7) is for a specific row i.e. applies to: =$G$7:$AN$7 and this highlights the specific cell that corresponds to the week of the first date that the communication was sent out.

    My first question:

    1) How do I create a specific conditional formatting rule using the =AND(G$4$D7) formula that fills in a cell every 30 days or 1 month and also corresponds to the correct week, leaves the remainder of the cells blank for that row, and that also ends when the end date in column E says? Will I have to have 2 separate formulas for each row with a monthly frequency? One for filling in the cells, the other for shading the remainder blank? Also, I should be able to specify in one rule that the formula applies to row 7, 10, 15, 22 for example in the “Applies to” section without having to make a new rule for each row, correct?
    2) What is the order in the rules manager that these rules should be placed? Should the original formula for all cells be placed at the top or bottom?

    Thank you in advance for the help.

  20. Ok think I have a mission imposable here, I need to do an “if, than conditional formatting”. So column “A” is the item “B” is the Due date and “C” is the delivery date. So I need the date in Column “B” to be Green if “C” is blank and “B” is greater than 120 days out, Yellow if “C” is blank and “B” is 60-120 days out and Red if “C” is blank and “B” is less than 60 days out. Is this even possible?

    1. Hi Chris,

      Of course, it's possible. If my understanding is correct, "greater than 120 days out" means more than 120 days from the current date. If so, you can create 3 rules based on the following formulas (assuming that row 2 is your top-most row with data):

      Green: =AND($C2="", $B2-TODAY()>120)
      Yellow: =AND($C2="", $B2-TODAY()>=60, $B2-TODAY()<=120)
      Red: =AND($C2="", $B2-TODAY()<60)

  21. I want cells to turn red if date is within 30 days of maturity date, turn green if within 15 days and turn yellow if within 3 days.

    For Ex if Maturity date is 07-Apr-2016, I need that cell to be red if date(current) is 07-Mar-2016 and so on.

    Please advise formulae to be used for above.

    1. Hi Nick,

      Assuming that the Maturity date is in cell A1, you can create 3 rules based on the following formulas:

      Red: =AND($A$1-TODAY()<=30, $A$1-TODAY()>15)
      Green: =AND($A$1-TODAY()<=15, $A$1-TODAY()>3)
      Yellow: =AND($A$1-TODAY()<=3, $A$1-TODAY()>0)

  22. Hi,

    I'm trying to build a gantt chart and I'm stuck with conditional formatting. I'm trying to fill up the total number of working days excluding the weekend.

    Basically I have,

    [dur][start][end=(start)+(dur)-1][completion-%]

    My current formula,
    This is to highlight the date
    =AND(AND(J$4>=$F8,J$4<=$G8),$A8"")
    This is to highlight the completion percentage
    =AND($A8"",$H8>0,J$4>=$F8,J$4<=($F8+$H8*($G8-$F8)))

    Is there a way to use skip the weekend?

  23. Hi,

    I have a large spreadsheet with a column containing dates, and need to have a column next to this one which names which year the date falls into.

    Eg, if a date is between 01/08/12 and 31/07/13 the 2nd column needs to say "year 1", 01/08/13 - 31/07/14 is "year 2" and so on.

    I'm sure this should be fairly simple I just cant work it out!!

    1. Hi Roy,

      You don't need conditional formatting in this case. Use a nested IF function similar to this:
      =IF(AND(A1>=DATEVALUE("1-Aug-2012"), A1<=DATEVALUE("31-Jul-2013")), "year1", IF(A1<=DATEVALUE("31-Jul-2014"), "year2", ""))

      1. Hi Svetlana,

        That's brilliant, I suspected I was doing something fundamentally wrong!

        Thanks for your help!
        Roy

  24. Hi,

    I'm creating a table for Targeted Dates and Targeted Times, based on Initial Dates and Initial Start Time. How do I format a cell, if using 24hr clock, if a time (midnight) falls into the next day? I need the cell to calculate to the following day, based on the time, but it should also remain the current date if time falls within the current time frame. Hope this makes sense.

  25. Hi,

    I'm creating a resource plan in which I would like to highlight that a resource is available for project work only 3 days a week.
    E.g.:
    Mr. A works every Thursday on A task (full year)
    Mr. A works every Friday on B task (full year)
    Mr. A is available for a project from Monday - Wednesday for project/sprint work

    I have hard coded formatting for initial 2 logic but not able to apply conditional formatting on 3 days a week. Based upon inputted dates, the formatting is coloring the whole duration however I don't want to apply formatting on Thursdays and Fridays.

  26. Hi -
    I am trying to create a visit schedule. I have formatted all of the cells to be in the date format. The initial cell is blank; however, when I insert formulas below to calculate other visit dates that are based on the initial cell a random date will populate. I would like all cells to remain blank until the initial cell is populated with a start date of my choosing. Please advise.
    Study Visit Visit Date
    Day 1
    Day 15 15-Jan-00
    Day 30 30-Jan-00
    Day 45 14-Feb-00

  27. and that is using todays date as the expire date.

    1. Hi Stacey,

      Select the cells in column C that you want to color, begining in C3, and create a rule with the formula =$C3<=TODAY()

  28. I am trying to find a formula to have the cell go red when it hits expiration date, but keeping all others that aren't expired to remain white. there will be only one date entered in c row 3.

  29. hello i wanna learn how overdue date move another sheet automatic and deleted current sheet

  30. Each time a worksheet is amended (each morning)it calculates labour time for the previous day, I want this labour time to occupy other cells on another worksheet for Monday through to Friday. I can get this to happen but I loose the previous days figures.The formula i have used is below, what should the false statement be to keep the whole weeks data in their respective cells.
    =IF(WEEKDAY(TODAY(),2)=5,'Cap Plan Data '!O8)

  31. I am trying to do the following:

    Track dates and identify if the date in the cell is

    Less than 14 days ago
    More than 14 days ago
    More than 30 days ago

    I have tried all possible combinations I can find, but cannot come up with an exact formula.

    Thank you

    1. Hello Leslie,

      Try the following formulas where A2 is your first row with data.

      Less than 14 days ago: =AND(TODAY()-$A2>0, TODAY()-$A2<14)
      From 30 to 14 days ago, inclusive: =AND(TODAY()-$A2>=14, TODAY()-$A2<=30)
      More than 30 days ago: =TODAY()-$A2>30

  32. Thank you for this post. I have a starting date for clients and need to call at one month after the start, 3 months, and six months. I would like the column to show a color if the call hasn't been completed and it has hit the 1 month, 3 month, and six month marks. I would also like it to not show a color if the call has been completed. Do you know how I can accomplish this?

    Column A would be the start date
    Column B would be the 1st month with the option to select completed or not completed
    Colmun C would be the 3rd month with the option to select completed or not completed
    Column D would be the 6th month with the option to select completed or not completed

  33. :) Thanks a Ton. have bookmarked your site. :)
    1 Facebook like from me in return. thanks again.
    2 removed ad filtering addons from the site.
    Best wishes.

  34. another question:

    how to know if two dates fall in the same month?
    thanks again :)

    1. Using the MONTH function :)

      For example, =MONTH(A1)=MONTH(B1) will display TRUE if the dates fall in the same month, FALSE otherwise.

  35. Thanks for such a quick reply. btw: what should be the format of the cell in which i want the results. i am also now looking into the link being suggested.
    thanks again

  36. i meant
    11/01/2013
    11/02/2013

    sorry for the double post..

  37. hi
    11/1/2013 0:00 is present in cell C3
    11/2/2013 0:00 is present in cell C2

    formatting of both is "mm/dd/yyyy h:mm"

    how can i find the difference/number of days between these two dates.

    Thanks in advance.
    eagerly waiting.

  38. I am trying to figure a formula for when how long it is taking cars to go through detail process from when they arrive. What I need is something that would turn the row
    Green for today plus 5 day
    yellow for 6-10
    red for 11+

    I have tried several and cannot get it to change the right color.

    Please and Thank you

    1. Hi Megan,

      Supposing that your dates are in column A beginning in row 2, you can use the following formulas:

      Green: =AND($A2-TODAY()>=0, $A2-TODAY()<=5)

      Yellow: =AND($A2-TODAY()>=6, $A2-TODAY()<=10)

      Red: =$A2-TODAY()>=11

  39. You may have answered this already, and I did not understand it. I am looking to format a spreadsheet so that when a date is entered in column B, three days later the cell in Column D highlights in red. In the days between, the cell should be highlighted in a lighter color.
    Thanks for your help!

  40. Good day, Svetlana,

    I've glanced through your forum to see if I could find a place where you've already answered, but saw one similar still blank. So I'll ask again for both of us!

    I have dated certifications that are due every year. However, for inspection, I can't put in the due date and use the common TODAY function that most people use to get 90/60/30 days out from being due. Mine has to incorporate the last completion date counting down to when it is due again next year. I'm looking for a formula that will use today's date, reference the column with last completion dates, and tell me when they are 90-61/60-31/30-1/0-past due. If you could help me and suggest which one of the new rule choices to select and enter it into, I would much appreciate it!

    Have a Great Evening!
    ~HLA

    1. Good time of day! :)

      Supposing the last completion dates are in column A, you can create rules with the following formulas:

      90-61: =AND($A2-TODAY()>60, $A2-TODAY()<=90)

      60-31: =AND($A2-TODAY()>30, $A2-TODAY()<=60)

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

      0-past due: =TODAY()-$A2>=0

      1. Svetlana, hello!

        I must thank you, Lady. I very much appreciate your expertise, and wish you a wonderful day!

  41. Hi!

    I've got to create a spreadsheet and I'm totally overwhelmed!

    I need to have future dates assigned to particular cells and I need to have those cells become highlighted a month before this date. Does that make sense and what would be the best way to do that?
    Best,
    M

    1. Hi Margarita,

      Supposing that your future dates are in column A and begin in cell A2, you can use the following formula:

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

  42. Hello,
    I am having issues with a date formula. I have a spreadsheet with an end date of 5/1/2015 in C3. I then have dates in column B which need to be conditionally formatted to turn green, yellow, or red depending on how far they are from 5/1/15. For example, my date of 4/30/15 needs to be green (one business day prior), 4/29/15 needs to be yellow (2-3 business days prior), and 4/27/15 or before (more than 3 business days prior to date in C3) needs to be red. I hope this makes sense. Please let me know if you need more information - I have tried everything I can think of!

  43. Hi,

    One of the best and most clear examples i have come across. LOVE IT! straight forward and clear!

    I do however struggle with the following.

    *Start Date*
    Confitional format for 3 - 6 months from start
    Confitional format for 6 - 9 months from start
    Confitional format for 9 - 12 months from start
    Confitional format for 12 - 18 months from start
    Confitional format for 18 - 24 months from start
    Confitional format for 24 months from start

    I have looked at your method for days but im unsure if this works the same for months.

    would it make sense to calculate days separately to then use that (hidden colomn) to do conditional formatting??

    Any advise appreciated.

    Keep up the great work

    1. Hi Alex,

      Thank you so much for your kind words.

      As for your task, you can use the Excel DATEDIF function to calculate the difference between the start date and other dates in months.

      For example, a rule with the following formula will format dates that are 3 - 6 whole months after the start date, inclusive:

      =AND(DATEDIF($A$1, $B2, "m")>=3, DATEDIF($A$1, $B2, "m")<=6)

      Where A1 is the start date and B2 is the top-most cell in the column of dates you want to format.

      1. Thank you soo much for the fast response and a solution.
        been wrecking my brain for 3 days with this.

        will respond back with the outcome.

  44. I am trying to set up a task tracker with forecast and actual dates. A date that is forecasted and is past due should turn red. a date that is forecasted and only 7 days away should turn yellow and any actual dates should remain standard font. Please help!

  45. Hi,

    I can't get the different colours to work, but highlighting in one colour is still a massive help. Thank you so much!

  46. Hi, Could you please help me!!

    I am trying to make a spreadsheet to show the first contact with a company (in the C cells), the F cells show the date 42 days which is the first follow up appointment due (which is where I need the colour coding highlighting system to increase awareness of the date) and the third column (G3, etc) will be the date the follow up appointment is completed (which will mean I no longer need the highlighting assistance).

    I currently have cells F3-F200 with the formula =IF(C3="","",C3+42) etc, to calculate the date 42 days on from the date in the C cells. I would like to put a conditional formatting formula on the F cells which tells me if the date is approaching. I would like it to be highlighted red if due within a week of the current date (and stay red if it goes past the date) and orange if it is due within 2 weeks of the current date. However, I would also like the highlighting to go away once the new date has been put in the column next to it (which will be G3-G200 - these cells have no formulas or data in them until).

    Please help, I'm so stuck!!

    Thanks :)

    1. Hi Nicole,

      Try the following rormulas:

      Red: =AND($G3="", TODAY()-$F3<=7)

      Orange: =AND($G3="", AND(TODAY()-$F3<=14, TODAY()-$F3>7))

  47. Good afternoon,

    I have a checklist spreadsheet that i am working on and need some assistance with conditional formatting. I have one column which has dates in it. I want to use an icon set for the following:
    When the date is today or any future date I want the green icon.
    When the date is within 14 days of today's date I want the icon to be orange - this needs to change and each day passes (a date 3 weeks from today would be green but as soon as it reaches the 14 days to go mark, I want it to turn to orange.
    Any date that is before today's date would be red.

    I can get the red and green to work but not the orange. Please help.

    Thanks
    Marc

    1. Hi Marc,

      The green and red icons are easy, please see the settings below. As for the orange one, sorry I cannot figure out a proper formula because relative cell references are not allowed for icon sets.

      Icon set

  48. Hi, im stuck, just learning excel. How do you create a formula that shows how many days left til the end of the month. Am assuming I use a NOW function, just not sure how to do it.
    Thankyou in advance. =)

    1. Hi Karen,

      You can use either formula:
      =EOMONTH(TODAY(), 0)-TODAY()
      or
      =DATEDIF(TODAY(), EOMONTH(TODAY(), 0), "d")

      To display the number of days, a cell with the formula should be in the General format.

      You can find more about calculating dates in Excel in our Excel Date tutorial. For example, the following article explains the details of the EOMONTH function that returns the last day of the month:
      https://www.ablebits.com/office-addins-blog/excel-month-eomonth-functions/#get-month-last-day

      1. Hi
        How to highlight all dates rows above a specific date.
        e.g all rows above 1 January, 2019.

        thanks

  49. Hello Svetlana,

    Can you please advise on my last question to you in post 116? Any help/suggestions would be greatly appreciated. And I can explain further if needs be.

    Thanks in advance,
    Leon

    1. Hi Leon,

      I am sorry I have very little experience with Google sheets. In Microsoft Excel, you could probably do this by using the VLOOKUP function (https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/)

      Though, I cannot state anything with confidence without seeing your data structure. If you could post this question on our forum and attach a sample Excel workbook for better understanding, our support team will do their best to help.

  50. HI,
    How can we highlight the data upto current month in conditional formats ..

    Thank you in advance.

    1. Hi Sai,

      Assuming that your dates are in column A and row 2 is the first row with data, you can create a rule with the following formula:

      =$A2<DATE(YEAR(TODAY()),MONTH(TODAY()),1)

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