SubscribeConditionally Formatting Dates in Excel [Part 1 of 2]
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
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,
- 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]
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
- Excel conditional formatting basics
- Using formulas in excel conditional formatting – 5 kickass examples
- Highlight Top 10 items in a list – using conditional formatting
- More tutorials on Conditional Formatting, Excel Dates, Excel Date Formulas
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.
| ||||
|
| ||||
|
Leave a Reply
 |
Sales Dashboards – Visualizing Sales Data – 32 Dashboard Examples & Implementations | Highlighting Repeat Customers using Conditional Formatting [Part 2 of 2] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
142 Responses to “Conditionally Formatting Dates in Excel [Part 1 of 2]”
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()))
@Rick.. My mistake. Thanks for pointing it out. I have corrected it now.
[...] 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 [...]
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
Dear Sir,
how to prepare the result chart on excel ?Please advice me?
Thanks
@Taj… what do you mean by result chart?
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!
@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.
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!
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?
@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.
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
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
@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.
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
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
for some reason the new formula didn’t paste right in my last post…should be
=(TODAY()-C10)
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?
@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.
i need a formula in excel in conditional formating, for dates that occure in the last 3 months. (not in the last month)
thanks
@Leonidas
Try: =M10>=EDATE(TODAY(),-3)
adjust M10 to be the first cell in your range
Really useful…far more helpful that MS Excel help facility…thanks.
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.
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.
@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/
Works like a charm!
Thank you very much.
Kevin
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!
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!
@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
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
@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
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.
@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
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
@Melanie
Try
=AND(A1-TODAY()>=0,A1-TODAY()<=6,WEEKDAY(A1,1)=1)
Brilliant. Except for some reason it will not format tomorrow, the 18th, but it will format the 19th! Any idea why?
Change > to >=
I am unsure where I change this, pls advise
@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)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
Kimberly
it will be like this
=and(x>=15, x<=30)
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?
Hi Hui,
I even tried modifying the Formula as follows but still no luck
AND((B1-TODAY())=15)
Ooops … my latest formula didn’t post properly AND((B1-TODAY())=15)
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)
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
@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.
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)
@JTiff
Are you sure that =AND(TODAY()>=A1,TODAY()< =B2)
is correct?
maybe it should be
=AND(TODAY()>=A1,TODAY()<=B1)
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!
@Antonio
What about:
=TODAY()-IF(WEEKDAY(TODAY(),1)<>2,1,3)
In my Excel this works fine:
=WORKDAY(TODAY(),-1)
Both formulas work fine.
Thanks a lot.
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!
@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.
Thank you very much Chandoo,, it’s a great help.
Have a great day ahead.
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?
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
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!!!
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.
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))
@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
.
=Today()+60
.
=Today()+30
.
=Today()
each of the 4 groups above will have a Conditional Format
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?
@Nancy
Yes
Try this
=AND((A2>=EOMONTH(TODAY(),-1)+1),(A2<=EOMONTH(TODAY(),1)))
adjust cell ref to suit
That works great- thank you so much!
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
@Fred
Add a Conditional Format so that cells value <> “”
eg: =A1<>“”
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!
@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
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?
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?
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
Hello,
How do I use CF to have a cell change color when a date is 5 years old.
@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
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?
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.
@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
@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
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?
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.
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.
=and(DateCell>today(), existing CF)
@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?
@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
@Hui
I have sent you the file. Thank you for your help!
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
@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
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
@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.
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.
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!
@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
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
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?
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
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
@Kyle
what about something like?
=INT((D2-C2)/7)Where D2 is the latest date and
C2 is the older date
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
@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
Apply
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
@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
@Hui
thank you very much Hui.. it worked perfectly..
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 …
AND FORMULA ONLY WORKS IF DATES ARE IN A SEQUENCE.. IF THEY ARE NOT IN SEQUENCE…CF DOES NOT WORK
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?
@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
I would like to know a formula(conditional formatting) to find out the year that appears frequently.
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
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
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!
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.
@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))
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.
@Wade
Try changing the order of the 2 CF’s in the CF Manager
if that doesn’t work please email me the file
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.
@Kate
Assuming your data area is A2:B100
The Conditional Formatting CF will be using an equation something like
=And(A2 < Date, B2 = “Active”)
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
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.
@Jaqueline
Do you want to cells that show
=A2+30
&
=A2-45
or a list of all dates between the two ?
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?
@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
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?
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
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
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
@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
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
I’ve tried this
=$H$2< ($W$2()-15)
with W2 being the cell fixed with a date i wish to use
still does not work! can anyone help… :’(
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.
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.
@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.
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?
@Kristopher: Assuming the dates are in A1:A10
For more on this, refer to http://chandoo.org/wp/2012/05/22/highlight-due-dates-excel/
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.
@Johnny… You can add a condition to check if a cell is a blank or not using something like this:
=AND(cell<>”",date_condition).
For more refer to, http://chandoo.org/wp/2012/05/22/highlight-due-dates-excel/
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()?
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
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?
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…
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?
[...] Conditionally Formatting Dates in Excel – How to format dates in excel based on a condition? | Chand… [...]