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

  1. I have a work/task tracker to use at my team meetings and I would like a 'bookmark' type identifier to show where this week is.

    The sheet has week ending dates (23/11/18, 30/11/18, 7/12/18 etc)across columns H:BP at row 4.

    To draw attention to the current week (so staff can easily see where to update the work progress) I have added conditional formatting 'Highlight cell rules' - 'A date occuring' - 'This week' to H4:BP4 to fill the cell matching a date occurring this week a particular colour.

    I want the corresponding column cells (H:BP)at row 1 to reflect the same font/fill colour (to make it easier to find)to change when the row 4 cell is highlighted. (This doesn't have to apply to the whole column - but would be handy to have as as option).

    I have previously done this (the cheeky way) with individual dates in the columns then adding 'TODAY' text in white font in all the cells in row 1 then applying the conditional formatting for row 1 cells to apply dark fill - hence revealing the 'hidden' white font in row 1. However I can't for the life of me work out how to apply this when using dates that are a week apart ('this week') and not 'Today'.

  2. Trying to make a conditional format based on a cells value of the date that has been inputted into that cell and have program shade yellow for +5 years beyond that date and then red for +ten years beyond that date. Example would be if I input 10/12/2015 in E4 I would like it to shade yellow at +5 yearson 10/12/2020 and then red +10 years at 10/12/2025.
    I would like to ba able to do this for any date that I input since I will be inputting various dates into my cell in column E.

    1. Hello,
      It looks like the following steps will do the trick for you:
      - Select the necessary range and go Conditional Formatting -> Manage Rules -> Add new.
      - Choose the "Use a formula to determine which cells to format" option.
      - Apply the formula like the one below:
      =YEARFRAC(F2, $E$4) >= 10 and =YEARFRAC(F2, $E$4) >= 5

      - Set the necessary color and hit OK.

      Hope this is exactly what you need.

    2. Hi Did you manage to find a solution for this? I have a similar problem trying to identify employees 5 years in the business 10 years in the business etc etc

    3. I need to do exactly this - were you able to find a solution?

  3. Help!!!!

    I’ve created a timeline in excel and want to have the conditional format fill in the color based on the start and end date. The headers I have are for the start of the week. Example the top header would show 10/22/18, 10/29/18, 11/5/18.

    And my Start date for a task would be 10/22/18 and end date of 10/31/18. So I need the cells under 10/22/18 and 10/29/18 to be highlighted. How can I do this?

    1. Hey Rosii,
      Did you get figure this out? I need to do a similar function and I am struggling.
      I basically have three columns: Allocation, Start and End Dates. So, If I choose a start date as 01/01 and End date as 06/01 at an allocation of 100% , then I need a function that will allocate 160 hours in six columns from Jan to Jun.

      Please help!!

  4. HOW CAN ENTER FIXED TIME FOR INVOICE DETECT THE DAY I AM USING AND NEXT
    NOT TO CHANGE WHEN OPEN DIFFERENT DATE NOT CHANGE

  5. Hi,
    i reviewed your all formula but not found any of them as i was looking for.
    can you let me know what is the formula i can use for getting 3 color in one cell. like below.

    yellow color - 10 days early of due date.
    Red color - if the due date has passed.
    Green color - If can meet the due date.

    Thanks /Zaman.

    1. You can use Home>>Conditional formatting >> Manage Rules >> New Rules >> and choose "Format only cells that contain" and choose Cell Value for less than 10 days , equal to 10 days , less than 10 days
      Hope this help to you

    2. You would use the formulas to highlight future dates, but apply 3 different rules to your columns.
      Red would be =$K3=1, $K3-TODAY()<15)

    3. Hi Zaman, im just wondering if you had any luck working this formula out? I am also needing a formula to highlight cells early of due date

  6. ALREADY EXISTING FORMAT - 13-Aug-18
    REQUIRED NEW FORMAT - 13-08-2018
    PLEASE GUIDE US HOW TO CHANGE THIS FORMULA

    1. If this is already formatted as a date, select the cells or column that you want to format, select "Format > Format Cells", select the "Custom" option, and enter the format "dd-mm-yyyy"

  7. I have a spreadsheet where I need to format an expiry dates in a column, based on 3 different dates (which can change)each date range will be shaded a diffener colour.
    i.e. red for dates pre and including 15sep18
    yellow for dates between red and green dates
    green for dates after and including 14Apr19
    Can you please help?

  8. need to apply conditional formatting with time.thanks

  9. i want to know as below :

    i have marked a conditional formatting on a particular cell , if the value in the cell exceeds a particular amount then it turns red , i want to know the date when the cell is showing red , so that it becomes an alertness

  10. good day

    Could you please assist me I have a spreadsheet with one culm for date and I want each cell to be automatic highlighted with red when it is 3 month old from the current date, yellow when it is 2 month old than current date and green when it is 1 month old from the current date
    Thanks in advance for your assist

  11. I have a Spreadsheet where i have to populate the cells for the arrival date of guests.

    At the top of the Sheet is the Date the report is for. i.e 7 June

    Then further down the columns I populate the names of guests / membership number and arrival date 7 June- which is the same as the date of the report.

    what is the code I can use so that when I change the date of the report it auto populates the arrival date of the fields beside all the guests who are arriving on the same date?

    1. Chris:
      If all the guests' arrival date will be the same date as the date for the report, you can enter the cell address of the cell from the top of the sheet, let's call it cell A2.
      So, in cell B12 where you want to enter a guests' arrival date the formula would look like:
      =A2.
      If you want to enter this address in the first cell that holds names and dates and then copy it down the column lock that address like this:
      =$A$2
      Incidentally, enter the names and dates in separate cells. Don't enter different data types in the same cell.
      You may even want to enter the first name and last name in two separate cells. This way you can search and analyze the records easier.
      Many of the questions here on AbleBits have to do with different data types having been entered in the same cell and now it has to be untangled. All that extra work and aggravation can be avoided by setting up the data entry correctly.
      If you want to get fancy you could create a Master List of all the members' names and then use a drop down or lookup field to enter their names. It should be faster and less prone to error. Oh, yeah, there are several options for lots of possible fun with this data.

  12. Hello,
    I am working with a spreadsheet with multiple columns that are mainly dates/milestones (for construction projects).

    Most of these dates are set at the beginning of a project and these dates can change. One very important date is the "completion date". For all columns/milestones, there is a column right next to it that has the option of A (actualized), P (pending) or N (non-applicable). Because these dates are for the most part entered manually, sometimes a date will get entered (zoning or building permits let's say) will get pushed past the "completion date".

    Is there a way to highlight cells that occur after the date of another cell? Additionally, can a rule be entered that only applies to dates with a corresponding P (pending) that occurs after the "completion date" and highlights those specific cells?

  13. Hello,

    I have a date (only month and year) let's say in cell A1 and I'm trying to have the cell highlight yellow if it's the month before the date entered and highlight red if it's the same month or later than the month entered. Could you tell me how to do that?

  14. Hi,

    I have a spreadsheet with start dates (A2) and end dates (B2) (manually entered). All end dates SHOULD be within 40 days of the start date, so C2 is '40 day date' (using A2+40). I now need to highlight all rows in which the end date falls outside of the 40 day limit. I've tried everything I can think of and all sorts of the ones on here, but nothing is working. Please can someone help?

  15. I have a question. So I have a spreadsheet with start day first column anniversary date second column, I need for third column to be 6 month to round up. so say I was at my job 1 year and 7 months I need it to round to 2 years.

  16. Hi,
    I have a spreadsheet with the week commencing dates going across the top of the sheet starting in cell H4.
    Is it possible to have a whole column highlighted when the current date is within that week commencing?
    Have been able to get just the date cell to highlight but not the rest of the column.
    Cheers

  17. Hi,
    I'm trying to format actual dates a task was performed vs when it was targeted to be performed.

    my formatting is:
    cells that contain blank is "white"
    cell value less than/equal target is "green"
    cell value greater than target is "red"

    the formatting worked well until I entered the July dates. Example: I have a target date of 01July18. the actual date performed was 01July18 so the cell should have turned green but it turned red instead. Am I missing something?

  18. Hi,
    I'm trying to format actual dates a task was performed vs when it was targeted to be performed.

    my formatting is:
    cells that contain blank is "white"
    cell value target is "red"

    the formatting worked well until I entered the July dates. Example: I have a target date of 01July18. the actual date performed was 01July18 so the cell should have turned green but it turned red instead. Am I missing something?

  19. I am trying to conditionally format cells to change colour when a time exceeds 17:00. I think that my problem may be that the cells containing the times I want to conditionally format are derived from another cell containing a date and time.
    Could someone please offer up some help, thank you.

  20. Dears
    I would like to make date ranges = Some text or month.
    Example : 5/01/2018 - 4/01/2018 = January
    5/02/2018-4/02/2018=February
    Kindly help me.
    Input two date ranges - Out put required "Text" (Bill of the month of)

    1. Sorry Date Column Singe only.
      Example
      01/01/2018 = December 17
      02/01/2018 = December 17
      03/01/2018 = December 17
      04/01/2018 = December 17
      05/01/2018 = January 18
      upto to
      04/02/2018 = January 18

      1. Khursheed:
        Where 4/1/2018 is in cell K25 and the date you want to compare it to is in L26 the formula looks like this:
        =IF(L26<=$K$25,"December 17","January 18")
        You can change the cell addresses to suit your needs.
        Note the cell that holds the 4/1/2018 is in K25 and in the formula is an absolute reference indicated by the $. This means that as you copy the formula up or down the column the cell to compare the dates to will always be 4/1/2018.
        The formula reads like this, If the value in L26 is equal to or less than the value in K25, then display December 17 otherwise display January 18.

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

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

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

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

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

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

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

  28. 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")

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

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

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

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

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

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

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

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

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

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

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

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

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

  42. Thanks you for details formatting concept explained.

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

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

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

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

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

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

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

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

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