Conditionally Formatting Dates in Excel [Part 1 of 2]

Posted on January 5th, 2010 in Excel Howtos , Learn Excel - 166 comments

This is first part of 2 part series on conditionally formatting dates in excel.

Conditional formatting is a very useful feature in Excel. You can use Conditional formatting to tell excel how to format cells that meet certain conditions. For eg. You can use conditional formatting to show all negative values in a range in red color. [Learn conditional formatting basics].

Today we will learn how to use conditional formatting to format dates.

Click on the below links to jump to relevant section.
Excel 2007+ – Conditional Formatting Dates
Excel 2003 – Conditional Formatting Dates

Excel 2007+ – Conditional Formatting Dates

Excel 2007 - Conditional Formatting Dates - menu

In Excel 2007, MS introduced several useful shortcuts to conditionally format dates. When you select some cells and click on Conditional Formatting button on ribbon and select “Highlight cells Rules” > “A date occurring”, Excel presents you quick shortcuts to frequent date criteria. This list includes options to format,
Excel 2007 - Conditional Formatting Dates

  • A Date if it is yesterday
  • Today
  • Tomorrow,
  • In the last 7 days
  • Last week
  • This Week
  • Next Week
  • Last Month
  • This Month
  • Next Month

Using this feature, you can quickly format the dates in your data meeting certain criteria.

This is very useful in situations where you want to highlight for eg. sales in last week. As the dates change, the highlighted values change dynamically.

Apart from these predefined date conditions, you can define your own conditions using formulas.

Excel 2003 – Conditional Formatting Dates

Unlike Excel 2007, there are no shortcuts for conditional date formatting in Excel 2003. You have to rely on Conditional Formatting Formulas to do this.

What is a conditional formatting formula?
In excel you can use formulas to determine which cells get the special formatting thru conditional formatting. For eg. a formula like =A1>50 applied over the range A1:A10 will highlight the cells with value more than 50.

So, to check if the date in cell A1 is yesterday, you can write a simple formula like,
=TODAY()-A1=1. [help on TODAY formula]

Excel 2003 - Conditional Formatting Dates

Here are some formulas to get you started,

  • To check if a date is in the last 7 days:
    =TODAY()-A1<7
  • To check if a date is in the current week:
    =AND(WEEKNUM(A1)=WEEKNUM(TODAY()), YEAR(A1)=YEAR(TODAY()))
  • To check if a date is in the current month:
    =AND(MONTH(A1)=MONTH(TODAY()), YEAR(A1)=YEAR(TODAY()))
  • To check if a date is in the last 30 days:
    =TODAY()-A1<30

[Help on AND formula, MONTH formula, YEAR formula, IF formula]

Using above formula based conditional formatting you can easily determine if a date meets a given criteria and highlight it.

A Practical Application – Highlighting Repeat Customers

Let us say you run a small retail store. And you want to give special discounts to all the repeat customers. In your mind a repeat customer is someone who bought twice from you in last 30 days. (If the person bought twice but the gap between 2 purchases is more than 30 days they are not repeat customers).

In tomorrow’s post I will show you how to highlight repeat customers using excel conditional formatting. Stay tuned.

Learn more about Excel Conditional Formatting

Join our email news letter:

If you like this article, please join our mailing list. You will get an excel tip every weekday. Also, you will get a free copy of my 95 excel tips e-book. Click here to join.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

166 Responses to “Conditionally Formatting Dates in Excel [Part 1 of 2]”

  1. Rick Rothstein (MVP - Excel) says:

    I think you have a typo in your 2nd date formula. You posted this…

    =AND(WEEKNUM(A1)=WEEKNUM(A1), YEAR(A1)=YEAR(TODAY()))

    I think you meant this…

    =AND(WEEKNUM(A1)=WEEKNUM(TODAY()), YEAR(A1)=YEAR(TODAY()))

  2. Chandoo says:

    @Rick.. My mistake. Thanks for pointing it out. I have corrected it now.

  3. [...] yesterday’s post we have learned how to conditionally format dates using excel. In this article, you will learn how to use these conditional formatting tricks to highlight repeat [...]

  4. Thanks for blogging about Excel and some of the conditional formatting features. I’m sure the folks over at Excel would appreciate you sharing your expertise with the community!

    Check out the Office page on Facebook at http://www.facebook.com/Office.

    Cheers,
    Kim
    MSFT Office Outreach Team

  5. taj kumar says:

    Dear Sir,

    how to prepare the result chart on excel ?Please advice me?

    Thanks

  6. Chandoo says:

    @Taj… what do you mean by result chart?

  7. tracy says:

    hi there

    how can i have a condition for a date is that is before today (meaning anytime the doc is open) if the date is before that then the cell format changes? i had a formula for this from 2003 but its not working when i pasted in 2007
    thanks!

    • Chandoo says:

      @Tracy.. welcome to PHD and thanks for your comments.

      Assuming the date is in cell A1, go to CF and select new rule > based on formula
      now type =a1<today()
      and set formatting

      This should work.

      • Mitchell says:

        Chandoo, how would do this for a column of dates? You don’t have to put a rule in for every cell that contains a date do you? Thanks!

  8. Jenine says:

    Okay I am using Excel 2007. I have created a monthly calendar using an array formula. I have used cf to highlight cell for if date is today and cf this month is in a three month time period. I want a cf to highlight dates that are holidays in which the college is closed. Can you help me?

  9. Chandoo says:

    @Jenine… lets say your holiday list (actual dates) is in the named range lstHolidays and assuming the calendar is in range A1:G5

    Now, select the calendar, go to CF > new rule

    select formula option.

    Now write, =countif(lstHolidays,A1)>0

    and then set formatting you want.

    This should work provided the range A1:G5 contains actual dates. Otherwise, you can easily convert the value in A1:G5 to dates using DATE() formula and then pass them to COUNTIF to check for holiday criteria.

  10. Prashant says:

    I want to change date format (01/10/2009)(dd/mm/yyyy) to 01-Oct-09 (dd/mmm/yy) but cell not convert to this format. if change format show 10-Jan-09. this cell convert from PDF and i dont change its formatting so please tell me whats we do.
    2nd option is that break this cell to 3 cell and joint with =date(dd/mmm/yy) format.
    tell me.

    and Thanks

  11. Ethan says:

    Is there a way to add values to the drop down menu for “dates occurring”…they have a bunch for the past and the close distant future…but don’t have anything beyond “next month” for the future….which is silly, ridiculous, but typical for Microsoft…most companies that use this feature for planning definitely plan things out farther then next month…

    if not, can you please advise on formulas for “next 3 months,” “this year”…and “next year”

    Thanks

    • Chandoo says:

      @Ethan… Welcome to chandoo.org and thanks for commenting.

      You can use formulas to check for these conditions.

      next 3 months = a1=median(today(),a1,today()+90)
      or =a1=median(date(1,month(a1),year(a1)),a1,date(1,month(a1)+3,year(a1)))

      for next year,

      =year(a1)=year(today())+1

      for this year,

      =year(a1)=year(today())

      All the best.

  12. Ethan says:

    thank you very much….i wonder if there is a way to add them to the drop down,….if i find out how i’ll let you know

  13. Ethan says:

    There is a slight problem with your equations for checking if a date in in the past 7 or 30 days…it returns a true value if the date is in the future…since today minus tomorrow is negetive, and therefore is still less than 7 or 30…if you change the formula for 30 days from =TODAY()-A1<30 to =(TODAY()-C190) you get the correct values even for a future date

  14. Ethan says:

    for some reason the new formula didn’t paste right in my last post…should be

    =(TODAY()-C10)

  15. John says:

    A conditionally formatted date cell that I believe is not possible.

    I have a column of dates, displayed as day of the week, then the date, then year. I wish to have data formatted based on the day of the week. For example, if the day is Monday to Thursday, it would be one color, then another color if it is Friday through Sunday.

    Is this possible?

  16. Chandoo says:

    @Ethan: good point.. thanks for correcting me.

    @John: Yes, it is possible. Assuming your dates are in column A, select all of them, go to CF, new rule and then write something like =Weekday(A1,3)<5 and format it in one color, add one more rule and say =Weekday(a1,3)>4 and set the color for weekend. That is all.

  17. LEONIDAS says:

    i need a formula in excel in conditional formating, for dates that occure in the last 3 months. (not in the last month)

    thanks

  18. Hui... says:

    @Leonidas
    Try: =M10>=EDATE(TODAY(),-3)
    adjust M10 to be the first cell in your range

  19. Philly says:

    Really useful…far more helpful that MS Excel help facility…thanks.

  20. DELORES says:

    I NEED AN EXCEL FOR DUMMIES ANSWER. I NEED A FORMULA TO ADVANCE THE DATE BY SEVEN DAYS. I HAVE 12 EXCEL WORKSHEETS (REPRESENTING JAN-DEC), AND 4-5 COLUMNS PER WORKSHEET (REPRESENTING HOW MANY SUNDAYS IN THE MONTH). HOW CAN I ADVANCE THE DATE FOR EACH MONTH SO I WON’T HAVE TO PHYSICALLY TYPE IN EACH SUNDAY OF THE YEAR (TOTALLING 52 TIMES). PLEASE HELP ME QUICK!!! THANK YOU IN ADVANCE FOR YOUR KNOWLEDGE AND ASSISTANCE.

  21. Kevin says:

    Im using a Gantt chart with a cell displaying the end date and another cell that displays the completion percentage of a task. I would like a formula that turns a cell red only if the end date is in the past and the completion percentage is less than 100.

  22. Hui... says:

    @Kevin
    Try Conditional Formatting
    apply a formula like assuming
    M10: Is Completion date
    M11: is Completion percentage
    =and(M10 .
    Refer: http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

  23. Kevin says:

    Works like a charm!
    Thank you very much.

    Kevin

  24. melanie says:

    Hi there.
    I am trying to use the below formula, but it will not format cells showing dates that have already occured this week.
    To check if a date is in the last 30 days:
    =TODAY()-A1<30
    The formatting should show dates occuring this week but after today in yellow, and cells that contain dates before today and for the last 360 days need to be red. Currently all dates including the past two days are still yellow. Please advise.
    Thank you for your help so far, it has been very useful!

  25. Norah says:

    I have a chart with a list of dates. I am trying to use cf to colour in the dates which are 3 months from today, I can’t figure out a formula. But I want to make sure everytime the document is opened the date is always updated so that it formats the dates 3 months from the actual current date. I hope somebody understands what I mean so my question can be answered. Please help!

  26. Hui... says:

    @Norah

    =Today() = Todays date
    =EDATE(Today(),3) is the date in 3 months
    .
    If you want to know is a Date = today + 3 months in CF
    assuming the cell is B4
    =B4=EDATE(Today(),3)
    Which will be true only when B4 is exactly 3 months after today()
    .
    If you want to know is a Date between today + 3 months in CF
    assuming the cell is B4
    =And(B4>=Today(), B4<=EDATE(Today(),3))
    Which will be true only when B4 is greater thand or equal to today and less than or equal to Today + months

  27. melanie says:

    hi there, thanks for all your formulas they work a treat. however please can you advise on the below. i want to use the year formula, but i need all dates in the past to show another colour. when i use the below formula it overrides the past dates formula:

    for this year,

    =year(a1)=year(today())

    Thanks,

    melanie

  28. Hui... says:

    @Melanie
    These formulas aren’t for use on a worksheet, they are for use in Conditional Formatting
    So you probably want a formula like
    =A1 < Today()
    which will be true when the condition is correct and hence apply your Format

  29. melanie says:

    Thanks for your prompt response.

    When I enter =A1 < Today() it is also formatting the empty cells.

    I have been using the below, to format cells that are in the past 360 days:
    =TODAY()-H7<360
    it seems to work.

    however, when I use the below formula to format forthcoming year dates, it does work, but it is also over-formatting the past dates that the above formula formats:
    =year(a1)=year(today())

    How do I resolve this?

    Sorry if I am not explaining myself very well! I am not a whizz-kid at excel (in case you can't tell!). Thank you for your help.

  30. Hui... says:

    @Malanie
    You need to apply two or three CF’s to the same range
    One for less than current date
    One for Greater than or equal to the current date
    .
    =TODAY()-A1<360 for cells within the last 360 days
    =A1>Today() greater than today
    =A1=Today() Equal to today

  31. melanie says:

    Hi there.

    I was about to admit defeat.. but they work! Thanks :)

    One last question, I need to format dates that occur from tomorrow to sunday, is there a formula for this? I have seen the formula for dates occuring this week – but this formula is not suitable as it overrides the formatting of dates that have passed already.

    Melanie

  32. Hui... says:

    @Melanie
    Try
    =AND(A1-TODAY()>=0,A1-TODAY()<=6,WEEKDAY(A1,1)=1)

  33. melanie says:

    Brilliant. Except for some reason it will not format tomorrow, the 18th, but it will format the 19th! Any idea why?

  34. melanie says:

    I am unsure where I change this, pls advise

  35. Hui... says:

    @Melanie
    Sorry I misread that
    Give this a go:

    =AND(A1-TODAY() > =0, A1-TODAY() < =6, WEEKDAY(A1,2) > =WEEKDAY(TODAY(),2), WEEKDAY(A1,2) < =7)

  36. Kimberly says:

    I am trying to create a Formula using Conditional Formatting … I have the 1st 2 parameters which are >30 days from today the Cell turns Green ; <15 days the Cell turns Red ; what I need is the formula for what's between 15-30 days to turn Yellow

  37. Hui... says:

    Kimberly
    it will be like this
    =and(x>=15, x<=30)

  38. Kimberly says:

    Hi Hui,
    Thanks but that didn’t seem to work … here are the exact formulas that I am using

    Due Date is more than 30 days out … (B1-TODAY())>30 … Cell Turns Green
    Due Date is less than 15 days out … (B1-TODAY())=15,B1-TODAY()<=30) am I missing a ( ) somewhere or something?

  39. Kimberly says:

    Hi Hui,
    I even tried modifying the Formula as follows but still no luck
    AND((B1-TODAY())=15)

  40. Kimberly says:

    Ooops … my latest formula didn’t post properly AND((B1-TODAY())=15)

  41. Hui... says:

    I did say it will be like this
    =and(x>=15, x< =30)


    So in your case it will be
    =and((B1-TODAY())>=15,(B1-TODAY())< =30)

  42. djain says:

    I have to 2 adjacent columns- projected date and actual date. And need to highlight the actual date which is greater than the projected date.

    Any help will be appreciated.

    Thx

  43. Hui... says:

    @Djain
    I’m going to assume that
    Projected Date is Column C
    Actual Date is Column D
    .
    Select Column D
    Conditional Formatting
    Apply using a Formula
    =$D1>$C1
    set format
    Apply
    .
    Adjust columns to suit.

  44. JTiff says:

    Hi, I understand how the concept of this works.. but I’m having issues doing this:
    I have 2 columns – a start date and an end date.
    I’d like them to highlight if TODAY’s date falls in between those two dates.

    Looks like this:
    A1 B2
    8/7 9/24

    Today’s date falls between that – so I’d like both cells to highlight.
    I did this statement, but it’s not highlighting both cells:
    =AND(TODAY()>=A1,TODAY()<=B2)

  45. Hui... says:

    @JTiff
    Are you sure that =AND(TODAY()>=A1,TODAY()< =B2)
    is correct?
    maybe it should be
    =AND(TODAY()>=A1,TODAY()<=B1)

  46. Antonio says:

    I would like to get YESTERDAY intead of TODAY. =today-1
    The tricky thing is that I only need works days. I mean, if today is wednesday give me tuesday in date format, but if today is monday, give me friday.
    any comments?
    Thanks in advance!

  47. Hui... says:

    @Antonio
    What about:
    =TODAY()-IF(WEEKDAY(TODAY(),1)<>2,1,3)

  48. Hans says:

    In my Excel this works fine:
    =WORKDAY(TODAY(),-1)

  49. Antonio says:

    Both formulas work fine.
    Thanks a lot.

  50. Franz says:

    Good morning. I hope you can help me on this:

    In a cell there is a date (expiration date of certificates) . . . in the cell next to it I want to have a remark that will automatically refer to the date and show if it is “Still Valid”, “For Renewal”, “Expired” or “Not Specified”. How can I do this using Conditional Formatting?

    Thanks in advance!

  51. Chandoo says:

    @Franz…

    Assuming the date is in A1, You can try this:

    In the next cell, write

    =IF(A1="", "Not Specified", if(A1>today()+14,"Still Valid", if(A1>=today(),"For Renewal", "Expired")))

    This displays the text you want. Then, you can apply conditional formatting and color the values based on what they are.

  52. Franz says:

    Thank you very much Chandoo,, it’s a great help.

    Have a great day ahead.

  53. Bernie says:

    Hi there,

    I would like to format a column with dates in it so that if today’s date is within 30 days of the date in the cell it will change color. Can you help me with this please?

  54. Kish says:

    I’m checking the date in Col. B2 if it’s more then 4 days ago (then todays date), if it is I’m setting the “fill” to red in Col A2 using this formula in Excel 2007 (using Conditional Formatting on the Home tab)

    =TODAY()-B2>4

    but if col B2 has no date (is blank) then col A2 is set to red – which I do not want. Can somebody help me, please

  55. roye says:

    Hi there. Am hoping you can help

    I have 3 columns. First if date of employment, 2nd is a date which is end of contract and the third is remark. The third column, I want a remark that will automatically refer to the end of contract date which could be “End of Contract”, “To be Started”, “For Rehire” and “On-Going Contract”. How can I do This? Please help..Thanks!!!

  56. Gaki Cojuangco says:

    HI, this is a great forum site. All question has been answered beautifully. My question is, I could I highlight all days that falls in friday in a calendar. I created a calendar but when i tried to highlight all the fridays using conditional formating and I selected the option equal to (text) friday it didnt highlighted any cells. please advice. Thank you.

  57. Steve M says:

    I’m having a problem using the conditional formatting. Let’s say I have a list of apartment leases that expire on different dates. How can I make each expiration date highlight as a different color depending on if they expires within 30, 60 or 90 days?
    Here’s what I’ve tried so far. I’ve pasted the below formulas into the rules within the conditional formatting. The problem is, it colors some of the cells but misses others. Even if they have the same date. Any help would be appreciated.

    G4=lease expiration date
    =AND(G4>=EDATE(TODAY(),0),G4=EDATE(TODAY(), 3),G4=EDATE(TODAY(),6),G4<EDATE(TODAY(),9))

  58. Hui... says:

    @Steve M

    Select your range of cells which you want to apply a Conditional Format to and clear the existing CF’s
    You will need to add a few CF’s to the range
    They need to be added so that the logic is correct in the order which they are listed, this can be changed in the manage CF dialog.
    Assuming your Dates are in G4:G20
    The CF’s will be like
    .
    =Today()+90 Stop if True
    .
    =Today()+60 Stop if True
    .
    =Today()+30 Stop if True
    .
    =Today() .
    each of the 4 groups above will have a Conditional Format

  59. Nancy says:

    Your formula to return TRUE or FALSE to evaluate when a given date falls within the current month is excellent. However I need to be able to identify all dates that fall in the current month or the next month. Is there a way to combine so that one formula can highlight these dates accordingly?

  60. Hui... says:

    @Nancy
    Yes
    Try this
    =AND((A2>=EOMONTH(TODAY(),-1)+1),(A2<=EOMONTH(TODAY(),1)))
    adjust cell ref to suit

  61. Nancy says:

    That works great- thank you so much!

  62. Fred says:

    Hi there, Similar problem to Kish for me, and couldnt see an answer to his queire. . .

    I have a column of data that I am formatting so that if it is above a certain percentage, then go one colour and below another percentage go a different colour. However, some of the cells in the column do not have data in them, but are still coloured as if they are below the benchmark percentage. I do not want these cells to be coloured. do i need to write a formula for the cells, or is there another way? I dont not want to have to individually delete the conditional formatting in each cell where there is no value
    Many thanks in advance

  63. Hui... says:

    @Fred
    Add a Conditional Format so that cells value <> “”
    eg: =A1<>“”

  64. william says:

    I want to format a range of cells (day 1 to 30 or 31 of any given month) in one column. Any given date (within the given month) will appear either none, once or multiple times (no limit of occurence) all on the same column. I want to group the occurence of these dates by color coding within each week they fall as determined by another range of cells (ex Sep 30, Oct 7, Oct 14, Oct 21, Oct 28, etc – this range is also in date format).

    I can do it with conditional formatting but I am limited to 3 conditions only which means i can color code up two 3 weeks only. (I am using office 2003). How can I possibly extend conditional formatting to at least 6 without using vba, anybody please? Thanks in advance!

  65. Hui... says:

    @William
    You can use a combination of Conditional Formatting (CF) which will allow 4 formats (3 Conditional + the Default Format)
    In the Default area you can use a Custom Number Format to say Change the color according to ranges
    The problem doing that is that it isn’t able to be linked and so each Custom Format will need to be setup manually
    .
    eg: If your CF handles Sep 30, Oct 7 and Oct 14
    you could add a Custom Number format like:
    [Blue][=40830]dd-mmm-yy;[Red][=40823]dd-mmm-yy;General
    40830 is 14 Oct, 40823 is 7 Oct
    .
    That will give you 5 custom formats

  66. william says:

    Hi Hui, thanks for the reply.

    However, the data that needs to be color coded is not always equal to specific date only. It must fall within 2 dates. For example, dates from range Sep 30 to Oct 7 is one set, Oct 8 to Oct 14 is another set, and so on. Which means if I have the dates Oct 1, 3, 4, 5, 6, 8, 10 ,14 ,15, if will format Oct 1, 3, 4, 5 & 6 as one color, Oct 8, 10 & 14 as another color, and Oct 15 as another color also.

    The conditional formatting I am looking for could also be used to format say for example, a data set containing multiple sets of sequential data (numbers or alphabetic set of data) where a defined range must identify all those data in excess of 3 conditions,
    e.g.
    numbers
    1-10=blue, 11-20=green, 21-30=red, 31-40=yellow, 41-50=orange, 51-60=gray, etc
    or
    alphabet
    a-d=blue, e-h=green, i-l=red, m-p=yellow, q-t=orange, u-x=, gray, y-z=purple

    On the other hand, is it also possible to use a combination of = in a custom format?

  67. william says:

    The last part of my question should read..
    On the other hand, is it also possible to use a combination of greater than, less than & equal in a custom format?

  68. jonathan says:

    Hello,

    I am trying to create a log for work and i want to use conditional formatting to automatically highlight rows based on the current week. Ex. It is week 46 of 2011 and I want all rows that have a due date of week 45 to be pink, all rows with a due date of week 46 to be yellow, all rows with a due date of week 47 to be green, etc.. I have a feeling Excel is more than capable of this, but i do not know how to setup the conditional formatting to make it possible.

    Thanks in advance

  69. Ray says:

    Hello,

    How do I use CF to have a cell change color when a date is 5 years old.

  70. Hui... says:

    @Ray
    Select the area of dates
    I am assuming A2 is the Top left corner of your date range
    Goto CF and use a Formula
    =A2<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))
    Apply a format
    Apply

  71. Regina Jordan says:

    I track service of our trucks. I have created a spreadsheet in which I record mileage and last service date and mileage. Another column has next service due. This could be a date (some trucks are serviced on date intervals) or a number (if serviced on mileage). I’ve been trying to conditional format this column but can’t get it right. I want dates before today to be red (mileage less than 0 automatically are red) – showing overdue, dates between today and 30 days or mileage between 0-500 to be green (due now), dates 30 days or more and over 500 miles no formatting needed. Everytime I set these conditions, it changes everything to red or doesn’t format at all. Any ideas?

  72. jonathan says:

    How do i conditionally format an entire row based on the value of one cell? I have a formula in place that will change the value of the cell based on the current date, however when the value changes i want the entire row to change a different color for each value. This is the formula in place: =IF(WEEKNUM(NOW())=WEEKNUM(K2),”Current Week”,IF((WEEKNUM(NOW())+1)=WEEKNUM(K2),”Next Week”,IF((WEEKNUM(NOW())+2)=WEEKNUM(K2),”ThirdWeek”,IF((WEEKNUM(NOW())+3)=WEEKNUM(K2),”FourthWeek”,IF((WEEKNUM(NOW())-1)<WEEKNUM(2),"PRIOR WEEKS","Lastweek")))))

    When the value of the cell is in its current week i want it to be yellow, next week to be orange, third week to be red/orange, etc.

  73. Hui... says:

    @Jonathan
    You select the entire row
    Apply CF based on a formula
    You have to add a new CF for each Color you want
    eg:
    =$A$1=1 apply Red
    =$A$1=2 apply Green
    =$A$1=3 apply Orange
    .
    It can’t be done in one pass

  74. Hui... says:

    @Regina
    You will need to add a new CF’s for each Color your are after
    Clear all the CF’s then add
    =or(DateCell =or(DateCell-30 etc

  75. Kevin says:

    this is probably obvious, but I just need to know how to conditionally format something based on a static date entry. I.E. I want to highlight cells occurring on or after 1/1/2011. this date will never change, so i want it entered statically, and i’m hoping i don’t have to enter it into a cell as a reference, but can just enter it directly into the formula somehow. Any ideas on the syntax for this? Do i just use the date code?

  76. Kevin says:

    Nevermind, answered my own question. Date code it is. For others wondering, I picked formula, and entered the following: (data affected is in column d)

    =D2>=40544

    Formula applies to D:D.
    40544 is date code for 1/1/2011. This can be ascertained by changing cell format to number temporarily. once you get the number for your static reference date, you can change the format back to the date type.

  77. Denise says:

    I have a spreadsheet that tracks days off for employees (sick days, vacation, jury duty, etc.) and I have the sheet set so that it automatically does a running total for me AFTER the week has occurred. The cells do NOT have dates entered in them. Below is a portion of the formula that I use to track the data

    =IF(NOW()>40915,COUNTIF(Paul!E13:H13,”=V”)+COUNTIF(Paul!E13:H13,”=0.5″)/2+COUNTIF(Paul!E13:H13,”=H/V”)/2)+IF(NOW()>40922,COUNTIF(Paul!K13:O13,”=V”)+COUNTIF(Paul!K13:O13,”=0.5″)/2+COUNTIF(Paul!K13:O13,”=H/V”)/2)+IF(NOW()>40929…..

    I have CF applied to these cells to change the color based on the text that is entered (i.e. “V” is filled with yellow, H/V is filled with gray) and it works just fine. How do I change the CF to apply only AFTER the current date has passed? I’ve tried a variation of my “If(now” formula, but CF doesn’t allow it.

  78. Denise says:

    @Hui
    I tried the formula you posted but all it does is turn the area I have the CF set for yellow, irregardless whether there is data in the cell or not.
    I want to try and set the CF the same way I have the totals set, where once the week has passed, the CF applies to the range of cells based on the criteria I have set. I DO NOT HAVE DATES IN THE CELLS OF THE SPREADSHEET. The spreadsheet is setup where the dates of the months are a header row for each quarter. Is this something that cannot be done?

  79. Hui... says:

    @Denise
    Can you email me the file with instructions on what you want to achieve
    My email is at the bottom of the page when you click on my name

  80. Denise says:

    @Hui
    I have sent you the file. Thank you for your help!

  81. Arie says:

    Hai all,

    I need a little help here. I am having data in 1 column let says A1 to A35. On cell A1 i put complete Date and time format (13-Dec-11 20:00), A2 downward are =A1+”01:00 with time (hh:mm) format only. After some cell downward, i will find the value of the cell is 00:00 which is actually 14-Dec-11 00:00. I want to make the format on this case will be full Date and time format, but i don’t want to edit the format manually because i might change the A1 value with different date and time. I looking for kind of “IF” function with this issue.
    Please help and my email is ris76122@yahoo.com

  82. Hui... says:

    @Arie
    Select the data
    Format the data using a Custom Number Format
    Ctrl 1
    Format Cells
    Number
    Custom
    and use a Custom Format hh:mm
    Apply
    .
    Now select the data again
    Add add a Conditional Format
    Use an Equation
    =DAY(A2) < < >> DAY(A1)
    and apply a Custom Number Format as d/mm/yyyy hh:mm
    .
    Enjoy

  83. Arie says:

    Hai Hui,

    Thanks for the tips, but i have tried your formula and it’s showing pop up message saying the formula contain error. Any idea?

    Thanks,
    Arie

    • william says:

      @Arie
      (This seems too late but in case you haven’t found the answer yet)

      I think what you are trying to achieve is only to add one hour increment on the date and time entered in cell A1 which you already did by the formula +”1:00″.

      To format the cell A1 as you have described above, go to format menu > cells (or Ctrl + 1 via keyboard) then select the number tab, from the categories select custom then type the following:

      d-mmm-yyyy hh:mm AM/PM

      If you have already displayed A1 as described here, just copy Cell A1 then paste special to cells A2 until the last row. From the paste special screen, select Format.

      • Arie says:

        Hai william,

        Thanks for the reply. What i am looking for is; i need to set the complete DD-MMM-YY HH:MM only on new days/date.
        For examples;
        if my A1 is 15-Mar-12 22:00, i want in A2 is only HH:MM (23:00) and again on A3 it will be 16-Mar-12 00:00.
        Since i might change the date and time on A1, so i am looking for formula which i can put any date and time in A1 with increment of “01:00″ for next raw with HH:MM format, but on the raw when the date is change i want it to be on full format DD-MMM-YY.
        Hope you get my point and can help.

  84. Cynthia says:

    Hi,

    I am trying to figure out which formula i need to use. I have a date in I5 and I need the cell to the right of I5 to highlight red 14 days after the date in I5.

    Thanks for any help!

    • Hui... says:

      @Cynthia
      Select J5
      Goto Conditional formatting
      New Rule, Select a Formula
      =Today()-$I$5>=14
      Set the format of the cell using the Format Button
      Apply

  85. Steven says:

    I have a column with a set of dates that are mostly in the future. The dates when people’s accreditations will expire. I want to be warning 6 months in advance of the expirey date. The built in Conditional and Formating only goes to a month. What’s the best way to do this?

    Thanks in advance

  86. Steve says:

    I have a column with a bunch of dates that are in the future. These dates are when people’s accreditations are going to expire. I would like to be warned 6 months prior to the expiry.

    What is the best way to do this?

  87. Ann Marie Zois says:

    Hi
    I have two spreadsheets, one that contains a running total week by week, the other that pulls in totals.
    I need to enter a formula in the one that pulls in the running total for the month based on todays date.
    Any help you can offer would be great.
    Thanks
    AM

  88. Kyle says:

    I have a cell with a date in it.

    In the cell next to it I would like it to show how many weeks ‘late’ that date is?

    i.e 1/2/12 is now one week late so the cell next to it show ’1′ and in another weeks time it will show ’2′ and so on….

    Thanks

  89. Maria says:

    Hello,

    I have a column of Expiry dates and I would like the date to change to red once the date is past. how can I use the Conditional formatting tool in Office 2007 in order to do this please?

    Regards,
    Maria

    • Hui... says:

      @Maria
      Lets assume your dates are in Column A from A2:A100
      Select the dates from A2:A100
      Goto Conditional formatting
      New Rule
      Use a Formula
      Enter the formula =A2 Assign a format
      Apply

  90. NABBEL says:

    Hi ….I have a column AE fillled with dates, i want this column to change colour if date becomes older then one week from current date.
    thanks

    • Hui... says:

      @Nabbel
      Select the dates in Column AE, I assume AE2 is the top most Date
      Goto Conditional formatting
      New Rule
      Use a Formula
      Enter the formula =AE2 < Today()+7
      Assign a format
      Apply

      • Nabeel says:

        @Hui
        thank you very much Hui.. it worked perfectly.. :)

        • Nabeel says:

          The only problem is.. if any cell in that column is left blank then..formatting does not work… it is only working if entire column is filled with dates with no blanks….
          hope you can help me again … :)

  91. Chelsea says:

    How could I use CF to track (1) all dates that are greater than today’s date (2) All dates that are past due and (3) dates that will expire this month?

    • Hui... says:

      @Chelsea
      You will need to add 3 CF’s one for each condition
      Assuming your data range is A2:A10
      select the range A2:A10
      1. All dates that are greater than today’s date
      Goto CF
      New Rule
      Use A Formula
      =Today()-a2<0
      set a format

      2. All dates that are past due
      New Rule
      Use A Formula
      =A2=TODAY(),A2<=EOMONTH(TODAY(),0))
      set a format
      Stop if True

      It is important that they are entered in this order and the Stop if True is enabled where noted, as otherwise the later conditions will override the earlier conditions

  92. Misha says:

    I would like to know a formula(conditional formatting) to find out the year that appears frequently.

  93. seyi says:

    please Help,
    i have have prepared a spreadsheet showing clients,amount paid and duration of service. i need a formula that would notify me if a client’s subscription is valid, due for renewal or expired. How will i do this?? your assistance is highly appreciated

  94. seyi says:

    hello please help,
    i have a spreadsheet showing client’s name, amount paid and duration of service. i need a formula that would notify me if a client’s subscription is “valid” , scheduled for “renewal” or has totally expired? your assistance is appreciated

  95. chloe says:

    Hi I am new with excel and in need of help. I have a list of percentages (currently at 0%) which represent the completion of construction. I need the percentages to turn red if they are less than 100% by a specific date. For example if the percentage is still at 0% when the construction was meant to be completed on 05/03/2012 then the number needs to turn red. Is there any way of doing this? Thanks in advance for any advice!

  96. Wade says:

    Hi. Amazing site. I have read all comments and found a few that were close, but not quite right.

    I have a sheet with colums that online classes were taken. I want to auto higlight each cell in the column when it becomes >11=12 months old another color, and dates<11 months another. This is so that I know when they have to retake the class. Please advise.

    I need it to be for the entire column, different dates in each cell.

    Thanks in advance.

    • Hui... says:

      @Wade

      You will need to add 2 CF’s
      1 for each of the 2 conditions

      Select the Range assuming A2:B100
      The 1st CF will be =A2 < TODAY()
      The 2nd CF will be =A2 < DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

      • Wade says:

        I dont know why, but it returns all cells to be the formatted color fill. Do I have to adjust anything in the formula you gave me? I am using =$C$2:$D$210 for the range.

        Inside that range it has dates like: 27-Sep-10 17-Oct-11
        11-May-11 11-May-11 10-May-11 11-May-11
        28-Sep-11 7-Nov-11 30-Jan-12 31-Jan-12
        6-May-11 9-May-11

        I need to have the cells turn red for the date that has past a year. I need the cells that are still within the year after the class date to be green. I need the cells that are between 11/12 months to be yellow.

        I am selecting the range;going to CF; new rule; use formula; entering your formula as above; formatting to turn red.

        I have been fighting this uphill battle for some time now. Please help.

  97. Kate says:

    Hi,

    I need to format cells based on date and current / expired.

    I’ve got a cell that automatically updates to show’s today’s date, and can format cells in a collumn which contain dates ealier than this, but I only want to highlight them if the cell next to the expired date contains a certain word (Active). Ive tried using AND and IF, but can’t get anything to work.

    • Hui... says:

      @Kate
      Assuming your data area is A2:B100
      The Conditional Formatting CF will be using an equation something like
      =And(A2 < Date, B2 = “Active”)

  98. Anca Bob says:

    Hi,

    I want a formula which colors the cell, whenever the date written in that cell is overdue (based on the today’s date). I need this in order to have a better preview on the tasks that are overdue by date.
    I tried formulas like: A1>date() or A1>now(), but nothing happened, even if the date was already due.

    I’m using x-cel 2007. Pls help. Thanks

  99. Jacqueline says:

    I need a formula that will generate dates in two columns; One 30 days post date in A2 and one thats 45 days prior then A2.

  100. Tracy says:

    I’ve used the CF to highlight due date cells to red if the due date is past today [cell value less than =NOW()], yellow if due date is between today & 30 days from now [cell value btwn =NOW() and =NOW()+30]. However, I have a separate column with completion dates. How can I format the due date column to NOT be red when the date is past today IF the completion date column has a date in it?

    • Hui... says:

      @Tracy
      You can use something like
      =and(Condition1, Condition 2)
      so both need to be True for the CF to work
      Can you send some more details of what cells and values you are using

  101. jade says:

    Hi There, i’ve read through every single one of these posts and i’m not sure i’ve located the answer i need.

    Ok so i’m trying to conditional format the following.

    The whole of column K will have dates inputted, these dates will vary dependant on inspection timescales and guidelines.
    Principally what i want is that all of the dates show green, when they are coming within a month of the due date they show yellow, and when they are within a day of the date or expired they will show red.

    Can anybody provide the formulas for this please?

  102. Keiley says:

    I am a real newcomer to Excel and the other posts confuse me, although probably answer my questions.  Can somebody provide me with the CF formulas for the following which I need to add to a long column of dates in Excel 2010.

    Cell turns Red if prior to todays date
    Cell turns Orange if 14 days before todays date
    Cell turns Orange if 60 days before todays date

    Many thanks

    • Nagesh says:

      Keiley, Suggest please use conditional formatting with 3 rules added, for further details, check the start of the page on how to do the same,
      Regards, Nagesh

  103. Tmus5 says:

    Hi,
    I am currently trying to get a cell to turn red yellow or green dependant upon if another cell has a value on and if it is after a certain date example will explain it better:

    I have cell A1 with a “C” for Critical “R” for Reccomended and “O” for Optional. In Cell A2 i have a date. I need to use conditional formatting to make the cell turn Red if it is “C” and more than 3 months after the date.

    Yellow for “C” and 6 months after the date

    and Green for “O” and 12 months

    the code i have and doesnt work is:  =AND(TODAY()-A2>90, A1 = “C”)

    why is this not working and yes the format is set to red and the data in the cells is : A1 = C and A2 = 24/05/2012

    The dates will all be different and i dont quite understand the TODAY function will this work correctly i need it to go from 90 days past the date set not todays date 

    • Hui says:

      @Tmus5
      There is something wrong with your formula
      but the one below works fine:
      =AND(TODAY()-A2>90,A1="C")
      Looks like the ” characters are wrong in your formula 
      If you have copied the formula from a web site always re-type the ” and – signs
      They sometimes get scrambled for characters that look right but aren’t

  104. Tom Simeone says:

    How do i go about having a fixed date..

    For example, as i’m going to be saving a spreadsheet each day, I can’t really use the TODAY function, I have a separate cell which i am going to enter the relevant days date.

    What i am trying to create is a way of using conditional formatting to look at the DATE in the cell, and then on the data in cells and if the date in the cells is 15days over the date in my cell, i want it to change brown,

    And after 30 days, change red. 

    I can do this with the ‘TODAY’ Function, but I need it to work on a specific date, not ‘TODAY’ Date, though it would be today, but it needs to be fixed
     

  105. Tom Simeone says:

    I’ve tried this

    =$H$2< ($W$2()-15)

    with W2 being the cell fixed with a date i wish to use 

  106. Simon says:

    Hi,

    It’s been an educating forum.

    please all, I want to format my worksheet as a date tracker to do this:

    For each succeeding day, 2working days will be added to give the required date for the cell.

  107. Carla says:

    I am trying to create a formula to make all dates red that occur after the 8th of each month, is this possible?

    Trying to determine in a year which monthly reports were submitted after the due date and this way, the date would jump out more if the color was different. Thanks in advance for any assistance.

    • Chandoo says:

      @Carla… assuming the dates are in A1:A10, select them and go to conditional formatting > New rule.

      Select rule type as “use formula…”

      Then, type =day(A1)>8

      Format in whichever way you want.

      Click ok and close.

  108. Kristopher says:

    I am trying to CF an entire column with varying completion dates. I would like those dates to highlight in red once they are 2 years old. How would I go about setting this up?

  109. Johnny says:

    I have a spreadsheet that displays training: DATE TAKEN and EXPIRY DATE.  I need a formula that will highlight cells BETWEEN 3 months and expiry date yellow.  Then I need a formula that will hightlight (in red) when the current date has passed the expiry date.  I need blank cells to remain blank.  I’ve scanned this entire site for a formula that will do this and nothing works so far or it highlights all blank cells a certain color.  pls help.

  110. Brian says:

    I have a column [EndDate] and I would like to highlight the column red should the date entered in that column be less than today().
    So, how do I write the conditional formula for this() cell?  Your example assumes the date is in cell A1, but I want my formula to cover all the cells in the same column.
    =Today() – thiscell() < 0
    What goes in thiscell()?

  111. Salman says:

    Hello! I want to automatically transfer my one cell value to another cell if date is greater then 31 and month is 1… Likewise on date 28 of month 2.. I want Formula for that can anyone help me For generating this Formula! Thanks A lot!
    =If(Day(A25)>31 AND Month(A25)=1, A20 = C20, A20 = A20)

    Friend.. Salman 

  112. chris says:

    I have a list of dates all weekdays of the current month I have extra cells sometimes since there’s more weekdays some months than others and cause of this the weekdays run into giving me a few dates into those few extra cells how can I stop those dates being displayed once the first weekday of the next month is reached?

  113. Vanessa says:

    Hello,
     
    I need a formula to highlight the cell once a given date has past 5 years, and another one when the 5 years – 30 days is coming up. Is this possible?
     
    Basically, 2009-02-25, turns red once it passes the 5 year mark, and then yellow once it is 30 days from the 5 year mark…

  114. Jamie says:

    I use 2003 excel and I have read and tried multipy diffrent conditional formatting replies. However it does not appear to work. I am trying to do (For example) column E Row 1237  I need 30 days from what ever date I enter the information in that cell to highlight in 30 days. I have tried formula First condition  formula is    is=isblank(E1237)=TRUE         Second Condition   formula is   -TODAY()-E1237=1237   &    Third Condition Cell value is   =AND(E1237>=TODAY(),E1237<=EDATE(TODAY(),1))
    What am I doing wrong?
     

  115. [...] Conditionally Formatting Dates in Excel – How to format dates in excel based on a condition? | Chand… [...]

  116. Roxanne says:

    Hi, I may be a bit late in posting to this thead, but I’m trying to create a schedule for 2014 for work. I’d like for the weekends and holidays to automatically fill so no one can be scheduled on those days. I was following another how to post where it gave me conditional formatting based on weekday date, but it doesn’t seem to be working. I’d like to input the start of the month in the worksheet and just have the highlighting auto adjust to the correct days for the weekends. I hope I’m explaining this okay. Any advice?

    • Roxanne says:

      I should add that all employees are down the side and all the days for each month go across the top. I don’t have these in date format, but I do have the beginning date of the month on the sheet. I’d like, for each year, just to be able to update the month/year on the spreadsheet to have the weekends highlight. Please help!!!

  117. Malia says:

    I am trying to take a cell that contains a date and have the cell turn yellow when today’s date is within 14 days before that date and red if today’s date matches or is after that date. If today is 6/24/13, then a cell with an end date of 7/5/13 should be yellow…and a cell with an end date of 6/23/13 should be red.

    Does that make sense? Please help!!!!

    • Hui... says:

      @Malia
      You need to apply Conditional formatting to the cell
      select the cell, I’ll use C3 as the example

      Home tab
      Conditional formatting
      New Rule
      Use a Formula
      =C3 < TODAY()-14
      Apply a Yellow Format

      Then apply a second CF
      New Rule
      Use a Formula
      =C3 > = TODAY()-14
      Apply a Red Format

  118. Sarah Jayne says:

    Hi ,

    I am having problems with the following and would appreciate the help!

    I have a table and I need to highlight a date red if it is in the past, I also need to highlight the date orange if it is within the next 8 weeks AND has no date in another cell.

    Any help would be great thanks

    • indzara says:

      Assuming the date you want to highlight is in cell A1. The other cell you check whether there is anything is in B1. I am also assuming you are looking for next 8 weeks as in next 56 days.
      You can use two conditional formatting rules.
      Rule 1: Highlight the cell red if =$A$1<TODAY()
      Rule 2: Highlight the cell orange if =AND($A$1=TODAY(),$B$1=””)

      I am sure there are other ways to do this. Hope this helps.

  119. Lorraine says:

    Hi
    I am trying to create a timesheet for employees and I need to highlight if a time range falls within or overlaps a time range. eg an employees start time in one cell A1 is 09:00 and finish time in A2 is 16:00 therefore the total in A3 is 07:00 (7 hours worked). They then work overtime but enter on the sheet in start time in cell B1 15:00 and end time in cell B2 17:00 and the total in cell B3 is 02:00 (2 hours overtime worked). However they can’t obviously start working overtime until after they finish their normal shift at 16:00 therefore the time in cell B1 is obviously wrong. I would like cell B3 (total overtime claimed) to turn red in this instance as there are conflicting times

    Hope this makes sense and hope you can help

    Thanks

    Lorraine

    • indzara says:

      You can add conditional formatting to cell B3 using the formula $B$1<$A$2
      i.e., Start time for overtime < End time for normal shift

      If you need to do this for multiple employees, we can easily replicate that. If you would like any help, please drop a comment.

  120. Lorraine says:

    Thanks for info, have got it working now!

  121. Michael says:

    I am trying to write a condition for a range of dates to turn a color if they are within 30 days from the current date and turn another color if the current date has passed.

  122. Terry says:

    Hi, I have multiple dates for licences that expire anywhere between 1 week and 5 years ahead. What formula can I use so that they stay green if in date. Amber in the last year and red when expired. I am using excel 2003

  123. Terry says:

    Hi, I have a excel 2003 spreadsheet with a mixture of dates that are due to expire anytime between a week and five years ahead. I need them to turn red when overdue, turn amber when within the last year and stay green when in date.

    For example a certificate expires in 01/07/2016. I would need it to go amber on 01/07/2015 and red on 02/07/2016

  124. Mark says:

    Hi, do you have formatting that applies to this situation:
    A value is expected to be entered into a cell during 15th and 30th day of the month (e.g. two equal payments). A cell will be highlighted when no data is entered into these cells on those dates.

  125. Lyka says:

    Hi

    I will appreciated for answer to questions. I have excel with due date eg 12-10-13 which have open with late and open with not late. How can I set the conditional formatting with 2 colors.
    thanks

    • Hui... says:

      @Lyka

      You want to use CF Formulas like:
      =and(Today()>=Date(2013,10,12), A1=”Late”)

      and a second CF
      =and(Today()>=Date(2013,10,12), A1=”Not Late”)

  126. Heidi Krueger says:

    What I am trying to do is a bit more complex. I have one column with a planned date on it. The second column has an actual date.

    I have conditionally formatted all other requirements except this final one:

    If column O’s date is older than today but is not blank, #N/A, – etc, and Column P of the same row is blank, then format the date in column O.

    I have tried and failed on these formulas:

    =IF(AND(O3<NOW(),O3<=NOW()-1825), (P3″”)) 
    =IF(AND(P3″”),O3<NOW(),O3<=NOW()-1825)

    Any help would be most appreciated!!

    Heidi

  127. Khalid says:

    I have a sheet for certificates, expiry date of certificates in One column. I wanted to Highlight he color of the CELL if the date will expire in coming 02 months.

  128. Ahmed says:

    I have a column with historical dates from 2010. It is literally a new row every day since 2010. I would like a formula that will highlight the LAST day of each month since then. Just the last day.

    • Hui... says:

      @Ahmed
      Assume your data is in B2:B100
      Select B2:B100
      Conditional Formatting
      New Rule
      Use a Formula
      =B2=Eomonth(B2,0)
      apply a Format
      Apply

      • Ahmed says:

        Sorry I meant I actually need the macro for it. I have done the conditional formatting, whereby I highlight the end of month data. I need a macro that allows me to do this as well as delete all irrelevant rows (everything else except highlighted row)

        • Hui... says:

          @Ahmed

          Try:
          Sub Delete_Rows_Unless_EOM()

          Dim LR As Integer
          Dim ColNumb As Integer
          Dim i As Integer

          ColNumb = 1 'Set the Column Number of the Column with the dates here

          LR = Cells(Rows.Count, ColNumb).End(xlUp).Row

          For i = LR To 1 Step -1
          If Application.WorksheetFunction.EoMonth(Cells(i, ColNumb), 0) <> DateValue(Cells(i, ColNumb)) Then _
          Rows(i).Delete Shift:=xlUp
          Next i

          End Sub

Leave a Reply