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

  1. Hi,

    I have an excel spreadsheet (2010) that I would like the date entered in a set cell to turn yellow within that cell three months before it's anniversary. Then to turn red on it's anniversary date.

    Any help much apprecaited.

    Thanks

  2. Hi,

    I need to check the Datetimestamp in an cell and condition format it with different color.

    Eg:
    Cell content will be in the below format ("22-MAR-15 23:15") ("DD-MON-RR hh:mm") format

    Scenario:

    o Green when process is complete by 3:00 CST/4:00 EST
    o Yellow when process is complete after 3:00 CST/4:00 EST and before 4:00 CST/5:00 EST
    o Red when process is complete after 4:00 CST/5:00 EST

    Applies to the current date.

    Can someone please help me on this.

    Thanks

  3. Hi Svetlana! Posted a comment previously, here are some changes to the question.

    Here are some details of the matrix:

    F1: Data Received Date
    G1: Duration (Days excluding weekends)
    H1:End Date (Days excluding weekends)
    I1: Data Reviewed Date

    G2: 3
    H2: 4

    What needs to be done is that: The date on F ( Data received) has to be added to the end date to calculate the deadline.

    If the deadline is met and completed, the cells at I(Data Reveiwed Date) should turn green.
    However, if the deadline is not met but it is still completed, it should turn yellow.

    I am unable to get the appropriate formulas. Do help, thank you so much.

  4. Hello, I have a .CSV file, which opens in Excel. In the spread sheet, column A is Subject and Column B is the Date. I want to use different colours in Column A (Subject), based on the value in column B (Date). What should be the formula. Thanks for understanding and cooperation.

  5. Hi Svetlana,
    I am creating a to do list to coordinate with the various items I have to complete by certain dates but I don't have the knowlege to make up the formula to return what I need.

    For instance;
    I will get a date or confirmation for a document to be filed and place that Date in A2.
    What I need to be able to do is have some cells return a value of 'due within the 15 days prior' and some cells to return a value of 'due within the 15 days after'

  6. I have a spread sheet with employee names and available certifications. The cells are marked with a check if the employee has the certificate. some certificates have expiration dates. I need a formula, for conditionally formatting the individual cells, that changes the color of the check mark when the certificate has expired.

  7. Hi Svetlana,

    I have a spreadsheet with date entry in range column (B10:B54) and I want that whenever Christmas date (24 Dec) and New year Date (31 Dec) comes irrespective of the year, both the date cells should get highlight.

    Thanks in advance

    1. Hi Navneet,

      Try creating a rule for cells B10:B54 with this formula:
      =AND(MONTH($B10)=12, OR(DAY($B10)=24, DAY($B10)=31))

      1. You are genius. It worked. Thanks a lot.

  8. I have task list with start and end dates. I'm trying to get Excel to be able to color fill the cells to the right of the dates based on the duration of the tasks.

    For example, Task 1 starts 3/16/15 and ends 4/3/15. The columns to the right are the Fridays of each week (3/20, 3/27 etc). For task 1, how can I get Excel to color fill the cells for 3/20, 3/27 and 4/3 and then stop.

    I tried Excel bar chart, but that didn't produce the intended results and requires a lot of reformatting. Thanks for any help you can provide.

    1. Hi Tom,

      You can do this by creating a rule based on the following formula:

      =AND(C2>=$A2, C2<=$B2)

      It assumes that row 2 is your first row with data, column A is the start date, B is the end date, and C is the left-most column with Fridays.

      You need to apply this rule to all "Fridays columns", not including the column headers.

  9. Hi! Okay so I've been given a matrix to complete and I AM STUCKED BECAUSE IM NEW WITH EXCEL.
    So, What i have is a planned date (D5), Duration (G5) and End Date (H5) and what i need is the Reviewed date which is on (J5) Is there formula if i want the values at J5-J100 containing the date to turn red when it is completed but over due
    And green is completed on time.

    1. Hi Estelle,

      If my understanding is correct, an item is considered over due if the Reviewed date (J5) is greater than End Date (H5). And you want to highlight only those cells that have a Reviewed date set (empty cells in column J are not highlighted).

      If so, then all you need is create a rule for cells J5-J100 with the following formula:

      =AND($J5<>"", $J5>$H5)

  10. I have a spreadsheet with titles from A1:G1, and data from A2:G283

    In column G i have drop downs with Yes or No options.

    I would like a conditional format which will highlight the rows (e.g from a2:g2) in red which have "no" in the G cell at the end of that row, and Green if "yes".

    What can I use for this?
    Thanks!

    1. Hi Tomocon,

      Simply select all the rows you want to highlight (without the titles) and create 2 rules with the following formulas:

      Red: =G2="no"
      Green: =G2="yes"

      1. Hi Svetlana,

        This seems to only highlight the first cell of the row. not the whole row.

        1. There seems to be a problem with cell references. Try this formula:
          =COUNTIF($C4:$G4, 0)>=4

          And make sure the rules applies to B4:H4 (you can actually include more than one row if needed).

          The row will be highlighted in there are 4 or more zeros in C4:G4, in any cells, don't know how to limit it to consecutive cells only :(

          1. Thanks Svetlana for the quick reply!

            In relation to my first question, the formula you suggested, will only highlight the first cell in the selected row.
            even though i have selected the whole row and made the conditional format on this?

            Any ideas on why?
            Thanks Tom

            1. Hi Tom,

              Oops, my bad. I mentioned incorrect cell references in my formulas but forgot to fix them. Sorry!

              In the formulas, please fix the column using the absolute reference (with the $ sign):

              Red: =$G2="no"
              Green: =$G2="yes"

              1. Hi Svetlana, Back again!

                I have a sheet, which has a data validation in Column I.
                I want to hide a row, If i select "Fully Sent" in this validation on that row.

                Any VBA codes i have found have hidden the entire range when I select "Fully Sent" in one row. I need this to only hide the row where "Fully Sent" is selected.

                Any ideas?

                Data validation in Column I
                Rows 4:92
                Other validation options "complete" "Pending"

                Thanks :)

              2. Thanks Svetlana!

                No worries at all! ... if you ever figure out the consecutive number issue please email me! thanks!

      2. Thanks Svetlana!

        So simple! I feel stupid!

        I have another one, I'm drawing a blank on how to do!

        I have a basic attendance sheet, for a weekly meeting.
        I would like to highlight a row of people who have not been present for 4 or more consecutive weeks.

        So highlight B4:H4 if "0" shows 4 or more consecutive times in C4:G4

        Thanks ! :)

  11. What would be a formula for the following example. I keep a spreadsheet with our data tapes that go offsite. After two weeks they expire and I need to request them back. The tape number is in column A, while the expiration date is in column D. I need to check column D and if it is equal to today's date, I need the tape number in column A to be copied to another worksheet. I have about 15 tabs I need to check every morning and thought this could be automated somehow. Thanks.

  12. Hello...I am having problems with formulas in columns. I tried conditional formatting and it isn't helping.

    I need a column to calculate the dates from 1 year forward and color coordinated to go from green to yellow to red the closer it gets to the deadline of 30 days and automatically update when I enter a date in the column to track the deadline dates...

  13. I want to create a conditional format that highlights any time that is less that 7:00:00 AM using AM/PM time. Any advice you can give me would be appreciated.

  14. oops. I just added my email. sorry

    1. Hi Sandra,

      No problem :) Assuming that your date is in column C, and row 2 is your first row with data, select all the rows you want to highlight (without the column headers!) and create a rule with the following formula:

      =$C2<DATEVALUE("6/30/2015")

  15. Hi, This should be simple enough but I'm a beginner. Can you please tell me how to highlight rows if the date in column 3 is any date before 6/30/2015. Thank you! Non-Employee Info 2/23/2015 6/30/2015 503441
    Non-Employee Info 3/1/2015 6/30/2015 512294
    Non-Employee Info 1/1/2015 3/5/2015 512237

  16. Hi how about instead of calculating the due date based on date, can you give an example based on time? like if the due time is 8:00?

  17. i m trying to calculate the difference between dates
    1st cell = 2015 February 9 01:07:13 UTC+5:30
    2nd cell = 2015 February 9 01:07:47 UTC+5:30

    2ND - 1ST

    pls can someone help me regarding that

  18. hi pls help me to highlight or to put icon to column N where i can identify data verified within TAT based on Column K (TAT date)..i only can put to a single cell how to do for entire column...bcos tat date is not same...tq pls advice me

    column K Column N
    tat date verified date
    22/01/2015 23/01/2015
    23/01/2015 21/01/2015
    23/01/2015 22/01/2015

  19. How to find total working day in a month including Saturday

    exp:may-2015, PH- 1day sunday 5 dyas, net working days 24 dys

  20. my question is first cell old date, second cell today date and third cell may be work completed or work in progress or work pending. but my condition, if third cell value is work in pending that time i need to subtract from second cell to first cell i need how many days work pending

    please help me for my daily activity works.
    thanking you,

    regarding

    ranjith

  21. hi ...
    can i vary content of entire column using universal formula based on contents of another column ???

    waiting for reply

    1. yes

  22. PLEASE HELP ME IN THIS ISSUE
    01-07-2013 A1
    01-10-2013 A2
    14-12-2013 A3
    01-10-2014 A4
    17-10-2014 A5
    01-03-2015 A6

    Now I want to fill like this automatically below cells D4 and E4:
    D4 E4
    01-07-2013 31-07-2013
    01-08-2013 31-08-2013
    01-09-2013 30-09-2013
    01-10-2013 31-10-2013
    01-11-2013 30-11-2013
    01-12-2013 13-12-2013
    14-12-2013 31-12-2013
    01-01-2014 31-01-2014
    01-02-2014 28-02-2014
    01-03-2014 31-03-2014
    01-04-2014 30-04-2014
    01-05-2014 31-05-2014
    01-06-2014 30-06-2014
    01-07-2014 31-07-2014
    01-08-2014 31-08-2014
    01-09-2014 30-09-2014
    01-10-2014 16-10-2013
    17-10-2013 31-10-2013
    01-11-2014 30-11-2014
    01-12-2014 31-12-2014
    01-01-2015 31-01-2015
    01-02-2015 28-02-2015
    01-03-2015 31-03-2015

    1. HI, FRIENDS I AM SUBMITTING MY PROBLEM LIKE THIS.
      DATE PAY Rs.
      A1: 13-07-2014 15000
      A2: 17-10-2014 17000
      A3: 01-12-2014 19000

      ABOVE SERIES IS TO MAKE SALARY BILLS IN FRACTION OF A MONTH AND
      Now I want to fill like this automatically below cells D4 and E4:

      D4 CELL E4 CELL PAY Rs.
      13-07-2014 31-07-2014 15000
      01-08-2014 31-08-2014 15000
      01-09-2014 30-09-2014 15000
      01-10-2014 16-10-2014 7742 ( FOR 16 DAYS @15000 P/M)
      17-10-2014 31-10-2014 8226 ( FOR 15 DAYS @17000 P/M)
      01-11-2014 30-11-2014 17000
      01-12-2014 31-12-2014 19000

      D4 IS ALWAYS FIRST OF THE MONTH UNTIL FRACTION COMES AND E4 IS ALWAYS END OF THE

  23. I NEED HELP!!! Trying to get cells to fill different colors based on date in cell for yearly (annual)certification.
    Example....cell E3 date is 1-JAN-15,

    **no action needed (green fill)1-JAN-15 to 1-OCT-15
    **must complete training in next 90 days(yellow fill)1-OCT-15 to 31-DEC-15
    **expired(red fill) 1-JAN-16 or after

    So if they completed cert on 1 APR 14...the cell should be yellow right now bc it is due to expire in the next 90 days.
    If they completed cert on 1 FEB 14 cell should be red bc it is past 1 yr.
    If they completed cert on 1 DEC 14 cell should be green bc they have 9months before the 90day window.

    Any help is GREATLY appreciated!!!

  24. Can some on please help me with this. thanks in advance.
    some cells in Column C have dates. I want to put a conditional format formula which will highlight the same line cell number in column E if there is any date in Column C.
    for example
    C2 has a date, then E2 becomes yellow
    C3 doesn't have date, E3 remain clear
    C4 has a date, then E2 becomes yellow
    and so forth

  25. hello! so I have a column were the names of certain permits are, and another for the date it was issued and another for the date of expiry. I want to have my cells with the names turn orange when there's only 2 months before it expires and green if it is still far from expiring, red when it has already expired. Thank you!!!

    1. Hello Lyka,

      Select your column with the names and create 3 conditional formatting rules using the following formulas:

      Red:
      =AND(C2<>"",$C2

  26. I am trying to highlight the days between start date and end date for a given task. tried conditional formatting does not work.

    I tried to just high light holidays. in one worksheet Match works, the same formula does not work else where strange.

    Can someone help in excel, how to go about this.

    1. Hello Raj,

      It's difficult to advise anything without seeing your data. If you can post your sample workbook on our forums and describe what data you want to highlight, our support team will try to help.

  27. Hi,

    I have a date a document was sent out in A1 when it is returned I will fill the date in B1. If B1 is blank and there has been more than 28 days since A1 I want it red, how do I do this please?

    egA1 01/01/2015
    A2 Blank but todays date is 01/02/2015 so its more than 28 days since A1

    Thank you

  28. Hi,
    How to set Mandatory Field in Excel.
    Eg.
    Column A Column B
    -------------------------
    Name (A1) (B1)
    Emp No.(A2) (B2)
    Address (A3) (B3)
    Here I want to set mandatory cell for (B1). need message box if there is no text on that cell. Please help me.

  29. Hi,in my spreadsheet where i want to apply formula in following way please help me
    in E5 cell i have expiry date. But i want 30 days before from the expiry date in E5, cell turn into RED color otherwise remain Green
    Example expiry date in E5 is 30 April 2015 till 31st March E5 remains Green but as 1st april arrives my E5 turn into RED

    BEST REGARDS

  30. I have a weekly time and accounts sheet that I would like to apply some conditional formatting too.

    Our working day starts and finishes at Midday. I would like to use the today function for automation purposes, is it possible to use the today function + time?

    What I would like to do if the above could be used is

    (Todays Date is merged across cells A1, B1 and C1)

    Highlight Cells A2:C10 if cell A1 (A1:C1) = TODAY + 12 Hours

    Many thanks in advance

    Rob Ward

  31. dear mam
    i want to highlight the particular year dates example
    1) 21-dec-2011
    2) 22-jan-2012
    so form this i want to highlight numbers dates which contains same year , month,date
    so that the entire row should highlight. help me mam.

  32. Hi there,

    I've been using this formula:

    =TODAY()-$A2>120

    To highlight any entry that is more than 120 days old. Now, I have to do that for like 600 entries and I'm confused, How can I select 600 entries in the same column and apply the same principle without having to do it to each and every entry?

    I would appreciate any help. Thank you.

  33. Hello conditional format users. I am at a loss on this one. I would like to highlight a cell if the current date AND time is between a certain range.

    For example i have a list of people on duty, and i wish to highlight the person on duty between 17:00 on 1st to 08:00 the next day. How can this be achieved. Any help would be appreciated

  34. I put the following formula in ( =CELL("format",$H3)="D4" ) it changes the color of the correct cells when the date is entered BUT when I take the date out of that cell all of the cells remains highlighted. Is there anyway to fix this?

  35. i have a problem where to make icon to a cell contain date based another dates in another cells....example i wan to make icon for column N based on column k. so i can differentiate how many data is verified within tat date...pls help me tq

    column K Column N
    tat date verified date
    22/01/2015 23/01/2015
    23/01/2015 21/01/2015
    23/01/2015 22/01/2015

  36. hi I want answer of these statement(1)a project complete 24days before its given date show the completion date please tell me the formula(2) display project completion date as 18-december-2014 project start 35 days

  37. Hi

    I have a spreadsheet of tenders that we are applying for. I want to highlight the cells using a Amber and Red system for the due date of the tender, where if Today is within 7 days of the due date it is Amber, and 2 days of the due date, it turns Red.

    I have done this using the formula suggested above, but what I would like to do is, once the Status cell has been changed to say "submitted" the colour changes to green.

    My current formula for the formatting is:

    =AND($F96-TODAY()>=0,$F96-TODAY()<=2)

    How do I need to change it to get it to do what I need?

    Does that make any sense at all??

  38. I have a spreadsheet that contains various dates stating when a task was accomplished that happened in the past. I need a formula that will fill the cell to various colors based on when they will be due for the same task but a year later ie. 90 days out to be green, 60 days out to be yellow and 30 days out red. The value in each cell only states when the task happened as opposed to when the next task will need to occur (essentially adding one year to the date in the cell). How can this be done? Thanks in advance

    1. Also, within 1 week or overdue to be black, sorry about that, thanks.

  39. I have a spread sheet that shows the months (example Feb 1, 2014) when a vehicle needs to go in for inspection; how do I have the cell with the date in it, change color at the beginning of the month stated in the cell, and then change back to the original color when the month has ended?

  40. follow up.. i tried this formula =WEEKDAY(D$6,1)>6, yes finally hi-lited the fridays but also hi-lited the last date of the month which is not friday, and Using this formula =WEEKDAY(C$6,1)>6 highlighting saturdays. 'want fridays to be hi-lited.

  41. hi SVET, I want only fridays as a weekend of the month, any solution using conditional formatting, your given example is for 2 days weekend and it's sat and sun. i tried this formula =WEEKDAY(DATE($C$3,$C$2,C$5),1)>6 but it was Saturdays being hi-lited, what i want is the fridays only, i tried more formulas but i failed. suggestion please.

  42. Dear Concern,

    kindly let me formula for finding days like if today is Monday so after 61 days what will day come?

  43. Hi,

    Is there a way to automatically fill dates to show only Monday, Tuesday, Thursday, Friday. For example, using the fill weekday option but removing Wednesday in every row.

    What I want to do is to show the following but to auto fill the dates for the whole year without Wednesday, Saturday and Sunday.

    5/1/15
    6/1/15
    8/1/15
    9/1/15
    12/1/15
    13/1/15
    15/1/15
    16/1/15
    etc.

    Thank You

  44. I currently have this formula:
    =AND(TODAY()I2,LEN(K2)=0)
    The formula works perfectly, but it only works one time. I need the formula to reset when I change the dates for G2. When I change the date for G2, it changes H2 and I2.
    J2 is highlighted for the dates between H2 and I2, but it isn't highlighted when I type in a date in K2. However, I change the date in G2 every 60 days and it doesn't reset the formula to work again. Any help would be greatly appreciated! Thanks!

  45. I have a column with various dates [column b]. I want cell B1 to populate the next date in column B after today's date. Cell A1 is =today() for today's date. Column B has dates based on a 64 day cycle starting in 1977 all the way to 2050. I want B! to show the next date after today in the 64 day cycle. Thank you for your help. Great site!

  46. I inherited this sheet from some one with format cell that can only accept date in particular month and year. please how can undo it. the cell has been format to accept 01/01/14 and this 2015 but it will not accept 01/01/15, it comes back telling me that I can only input date within this month.

  47. I am tryimg to write a formula that says if the value of a specific cell is a 1 or 2, add 60 days to the date of another cell and place that date in a third cell. but if the value is 3 add 30 days to the date.

    So if A1 is a "1 0r 2" take the date in cell A2 and add 60 days to it and place the value in A3. If the value of A1 is a 3, take the date in cell A2 and add 30 days to it and place the value in A3

    1. Hi Mike,

      The following nested IF formula works a treat. Just copy it into the cell A3 and check if it returns the result you want:
      =IF(OR(A1=1,A1=2), A2+60, IF(A1=3, A2+30,""))

      Please make sure the cell A3 has the date format (you can simply copy the format of A2 into A3).

      Also, if the cell A1 contains any value other than 1,2 or 3, the formula will return an empty string.

  48. hello swetlana,

    i want to highlight my dates three days before into green and into red after date is expired.

    thnx in advance.

  49. i want to highlight my dates three days before, how can i do it pls..

  50. Hello, I can't figure out solution to my problem. I have a table with increasing date rows like this:
    Date Value
    3.11.2014 356540
    4.11.2014 356757
    4.11.2014 356758
    4.11.2014 356762
    5.11.2014 356793
    6.11.2014 356833
    6.11.2014 356838
    Is it possible to format that table so the colour of font (or whatever) changes randomly after every change of date. So the rows with same date will be differently highlighted? Thanks in advance, T

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