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

  1. I am trying to do the following:

    Header Row 12/1/2018 1/1/2019 2/1/2019
    Columns
    Start Finish
    12/1/2018 1/1/2019
    12/1/2018 1/1/2019

    I am trying to compare the dates in the Column "Start" and "Finish" to the header rows and conditionally format the color based on whether the date is within range or not.

    Is this possible with Excel?

  2. Hi there
    I need help with a future date formula. I need a policy to expire every 3 years (2019-2022), rather than annually. I need this to show as GREEN when in date, YELLOW a month before its due to expire, and RED once this has expired. Which formula do I use to cover these formatting? I've tried it the normal way but used previous dates (2015-2019) to get the desired results, but unsure how to get this to work for the desired current/future dates 2019-2022. Whenever I apply the same formula/formatting my policy which is in date, stays white.
    Any help would be greatly appreciated.
    Kind regards

  3. Hi,
    I have been trying to figure out the necessary formula for conditional formatting for the past few weeks and have been unable to locate it.
    I am working on fixing the conditional formatting for "Audit Due Date Tracking". In boxes C & D, there is a formula set up to show 2 weeks (14 days) from C="Sent" and D="Due(2 weeks)". What is needed is conditional formatting to display "PINK" in the applicable rows before the 2 weeks are up and is at 12 days to indicate that an email needs to be sent to the recipient. Next, conditional formatting for E="Received Date" needs to be set up so that "YELLOW" shades the rows that are past the 14 days; indicating that they are all past due.
    Any help someone can offer is greatly appreciated! Thank you in advance! =)

  4. Hello, I want to run a program in my excel file. Which contains three columns and 18000 rows. To, simplify my data i want to select the vales like 0,0.5,1,1.5 etc. and against these values i want to select the whole row. how can i do this by using one simple formula in EXCEL.

  5. I have created a spreadsheet tracking multiple tasks that are all carrying a date. Many of the cells dates a re defined formulas to define the next task day being x number days. Current conditional formatting is red for past todays date, green for within the next 2 weeks, and just plain for past the 2 weeks. How can I highlight and keep the past date in a cell of the completed task and still maintain the links driving the future dates. Essentially want to show the task cell in yellow as completed but keep the date intact. Today because it shows in the past it is red and would override the color if I just changed it to yellow. I can type in complete in the cell and change it, but hen the future days error out of course due to the existing formulas driving the future dates Thoughts?
    Thanks...assuming it makes sense.

  6. Hi -
    I am trying to format a cell that if the date in the cell is after todays date and after cell A17, it should highlight. Any ideas?
    Thanks in advance!

    1. were you able to figure this out? I am looking to do the same thing.

      Thank you!

  7. i want create Excell sheet For Apartment Rent Details
    Example: We Have 6 Apartment.each apartment Payment in 3cheques
    i want know apartent1,2,3... Payment details.next payment when will due

  8. Hi

    I'd like to know how to do the following:

    Have a table with the headings:
    Task name
    Start Date
    Duration
    End Date

    I want to be able to enter the start date and duration and it to automatically work out the end date. This is so that whenever I change the start date, it updates the end date based on how long it takes.
    In addition, is there a way to make it take into account weekend days? As in, if I input a duration as 10 days, I mean 10 working days (so two working weeks). If not, this isn't the end of the world; I'm just curious.

    Thanks in advance!

  9. I am looking for a formula that will allow me to conditionally format cell colour based on date. For example if our delivery date is today, I want to colour cells based on whether the delivery was on time (green), 1 day delay (yellow), 2 day delay (orange), 3+ day delay (red)
    ie:
    Data Period End Day 6 Delivery Date
    Jan 5/19 Jan 11/19

    so if Day 6 Delivery was done on Jan 13/19 instead of Jan 11/19 I want the cell with the Jan 13/19 date to be coloured orange

  10. Hello, Is it possible to apply a time format to a cell only under a specific condition? I would like for any result below a value of 1 to appear in a time format (hh:mm), like 3:30 or 1:00 instead of 0.1458 and 0.0417. I need the rest of the column to stay unchanged though. It contains text value like "2-day" and "6-day". How to create such a conditional formating? Thanks for your help.

  11. Thank you for your posts, they are incredibly helpful. I've been wanting to figure out how to do this for years.

  12. pls tell me which formula use to before one day cell automatically color for delivery status

  13. Can someone please help me. I am trying to create a formula that will highlight the call yell onw 1 year from the date in the call.

    I.e. if one of the dates in cell N is 5-Jan-2020
    Then on 5-Jan-2019 the cell will turn yellow

  14. Update - I have used the COUNTA function, but that counts all cells with text and I want to specify Holiday & Half, but COUNTIFS doesn't work?

  15. Hi,

    I have a spreadsheet that covers the whole year for holidays and deducts them from the persons allowance. What I would like to know is, if at the end of January I want a column that calculates how many days have been taken since 1st January (E6), how do I create a formula in column AA6 that counts full days and half days holiday (i have formulas set up to record these)? Any help would be really appreciated.
    Thank you.

  16. hi
    i need to highlight one row when the first three columns of dates are came to be similar

  17. I have range of dates with time updated in a cell having 500 records. I want only the only the dates to be highlighted for the time between 6pm-8am. my first record will be highlighted because time falls between 6pm-8am and second record is starts with 4pm so it is not highlighted. Please anybody get me the solution using conditional formating or filtering or any other method, but don't split the date and time. if you giving in vba also great or in excel. Awaiting for your result solved as early as possible will be very much grateful to you.

    Example

    12/13/2018 21:28
    12/13/2018 16:02

    Thanks and regards
    J. Prasad.

  18. I want to format my spreadsheet so that any date more than 365 days old from todays date turns Red, while all other current dates turn Green when entered into any cell.

  19. Thank you!

  20. Hello,

    How do I change the color of a field when it's date is "overdue" based on the date in another cell. Let's say the date in B2 is 12/3/18 and a field is automatically populated to show a due date 7 days away in B3 is 12/10/18. How do I get that cell (B3) to turn red if the current date is equal to or pass that date (showing that it is now late). The date in B2 will never be the same.

    I hope that makes sense.

    Thank you

    1. Hello, Del.

      If you need to set a conditional formatting rule to change the cells' color based on another cell value, you may find this article helpful.

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

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

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

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

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

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

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

  28. need to apply conditional formatting with time.thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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