See how to apply Excel conditional formatting to dates. Learn how to use formulas to highlight weekends and holidays, format cells when a value is changed to a date, shade upcoming dates and delays, conditionally format dates based on the current date, and more. Continue reading
Comments on: How to conditionally format dates and time in Excel with formulas and inbuilt rules
by Svetlana Cheusheva, updated on
Comments page 12. Total comments: 1243
On conditional formatting want to set a rule to highlight cells with dates after a cetain date. Example highlight cells with months, in different color per month, if possible, afer 30 June 2019. If not, then highlight dates after 30 June 2019. Thanks. Look forward to your swift response.
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use the DATE function to compare the value in a cell with the desired date — DATE(2019,6,30)
I hope it’ll be helpful.
Hello Svetlana
In the excel table I have a column that contains many different numbers from 0 to 3000 or more
I want to format the numbers. Each number takes the color of a line and the borders of a cell in a color that differs from the other number and is equal to the same format if the number is repeated
Thank you!
Madam,
daily weekly monthly activity tracking
I want to know how to create daily weekly monthly activity details which has to show going expire date and yet to complete within these days(Ex: 2days it has to complete).
Please am searching answer for this past 3days am not getting answer in the google
Hello!
Please describe your problem in more detail. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.
In a column I have incremental dates which is incremented by 3 days from it's previous row. These 3 days are days required to complete any particular task which is independent with each other. At a time I can perform only one task irrespective of sequence. Lets assume the dates are as follows to complete the tasks:
01-Jan
04-Jan
07-Jan
10-Jan
13-Jan
16-Jan
19-Jan
22-Jan
Above dates can be obtained by simply adding +3 to previous cell and drag the formula downwards by this way I can get the date when my work will be finished.
Now lets consider the activity in row 2 & 5 are complete, the moment I put Done in any cell next cell should automatically updated with previous date + 3 days and accordingly all dates in all column. What will be the formula to auto calculate end date to finish my work in each cell. For example
01-Jan
Done
04-Jan
07-Jan
Done
10-Jan
13-Jan
16-Jan
Pls. suggest
In a column I have incremental dates which is incremented by 3 days from it's previous row. These 3 days are days required to complete the task which is independent. Lets assume the dates are as follows:
01-Jan
04-Jan
07-Jan
10-Jan
13-Jan
16-Jan
19-Jan
22-Jan
Above dates can be obtained by simply adding +3 to previous cell and drag the formula downwards by this way I can get the date when my work will be finished.
Now lets consider the activity in row 2 is complete, what will be the formula to calculate end date to finish my work. For example
01-Jan
Done
07-Jan
10-Jan
13-Jan
16-Jan
19-Jan
Pls. suggest
Hello!
I don't quite understand your explanations. But you can use the ISNUMBER function to determine if a cell contains a date.
=IF(ISNUMBER(A1),A1+3,"")
Hope this is what you need.
Pls. ignore this question, I have send new question for easy understanding.
Hi
I have a 4 week school timetable (Week A1, A2, B1 and B2) on one tab that links into another tab with the whole staff timetable on it. When the staff initials are added into a cell it automatically shows their timetable. What I am trying to do is highlight in green today's lessons in that 4 week timetable structure so teachers can see which lessons they have each day.
To help me with this I have a third tab with the dates of each day in each week. So, today, for example, is Wednesday of Week B1. How do I conditionally format the relevant timetable on the '4 Week Timetables' tab to automatically move the colour coding to the respective part of the timetable?
My logic goes something like this:
When today's date equals what is on the 'Whole Term Dates' tab, cells d2, d7, d11 (etc.), then make the '4 Week Timetables' tab, cells h8:j8, green.
I hope this makes sense and that you are able to help. Thanks very much for any time you spend in this. I really appreciate it.
Regards
Simon
Hello, I have a sheet where it highlights a products expiry date if it is within 30-60 days of todays date Yellow, Red for 1-30 days Etc. This is set up as a conditional format based on another cell calculating the days. How do I get the formula within the conditional format rules so I can entirely remove the Column doing the calculations to improve the speed of my document? Thank you.
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
Pay attention to the following paragraph of the article above
Hope you’ll find this information helpful.
I have a spreadsheet that keeps track training. One requirement is to complete certain parts within 2 years of employees hire date. I have the start date in one cell, under the employee's name and I have a column for date complete. Want to fill the date complete cell with colors depending on if they have gone over their 24 month due date, if they are within 6 months of their due date and when they have a year left on their complete date. I believe I need and If AND formula where I can say if the date complete cell is blank and the hire date is either greater than or equal to the 730 days, 550 days or the 365 days, it fills with the appropriate color. Unable to get my formulas correct to do so. Can you help?
Hello!
I hope you have studied the recommendations in the above tutorial.
Pay attention to this section.
Please specify what formula you used and what problem or error occurred.
Hello,
I'm trying to have the cells for column G turn green if there are dates entered in columns E and F. If there are no dates entered I want the cells in column G to be red. If there is one date entered in either cell E or F, I want the cells in column G to yellow.
Thank you for your help.
Hello!
To conditionally format a cell with a date, you can use the formula
=LEFT(@CELL("format",A1),1)="D"
It returns TRUE if any of the standard date and time formats are applied in the cell.
Hi
I need help!
I'm trying to come up with an Excel dashboard whereby the end result will be traffic light.
The input for this traffic light which to be displayed on the dashboard will be project completion (%) vs it's Go-Live/Handover date.
The traffic light on the dashboard is meant to tell you whether you are on track or not when the project completion is measured against the time you have left.
I've tried using several formulas but none worked and I'm no expert in Excel. Hope some of you can help me out here!
Thanks!
Hello! I am creating a calendar in excel (and trying to avoid using vba, since I know nothing about the programming side of things!). What I am trying to do now, is use conditional formatting to highlight(/grey out) dates not in the current month. I saw on another site this formula:=MONTH(B5)MONTH($B$6), where B5 is the month dropdown list.
Currently, my spreadsheet has this in it:
1. dropdown lists for year in B2, and month in C2.
2. A formula to change the dates based on the selected year and month, which is copied to all the "calendar boxes":
=DATE($B$2,MATCH($C$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1)-WEEKDAY(DATE($B$2,MATCH($C$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1),2)
3. I also have this rule:
=B6=TODAY() which applies to my "calendar boxes", range B6:H41. It highlights the current day in blue.
Any help would be much appreciated!
Hello Katie!
If I understand your task correctly, the following formula conditional formatting should work for you:
=IF(AND(MONTH(B1)=MONTH(TODAY()),YEAR(B1)=YEAR(TODAY())),TRUE,FALSE)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi! Can anyone help me with this concern? :) I was trying to find the formula where the cell will be highlighted in the specified colors below when the ff conditions are met:
EG: The exact date or deadline is July 24, 2020.
2 days before deadline (Jul 22) = yellow
1 days before deadline (Jul 23) = light red
deadline (Jul 24) = red
overdue (Jul 25 onwards) = dark red
Is there any way we can do that in googlesheets? Thank you for the help!
Hello!
I hope you have studied the recommendations in the above tutorial.
There are answers to all your questions.
Describe in detail what problem you have, and I will try to help you
DATE :15/07/2020
S.NO DATE DC NO FINANCE DC NO CUSTOMER NAME VEHICLE MODEL CASH MODE DOWNPAYMENT CHEQUE AMT DO NUMBER
please help one this. i have mentioned colums in excelsheet. for me if date is more than 5 days if cheque amount not received then it should be popup in red colour otherwise in green color. please send me formula in my email id.
Thanks
Hello!
Check out the recommendations in the article above. Also read about using formulas in conditional formatting. If after that you have specific questions, I will try to answer them
Hi Alexander, Thanks for the guide, Really Helpful!!
I have a question regarding conditional formatting dates which have passed due, only if the the task is not close or not completed.
For Info Start Date - Column C, End date - Column D, Task Status Column H, Today's Date - Cell J1, Date format dd.mm.yy.
Many Thanks In Advance
Becki
Hello Becki!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
Hi Alexander,
Apologies for the vagueness in the original post.
please see below sample data:
Task Name Start Date End date Status today's date =TODAY()
task 1 29.06.20 29.06.20 completed
task 3 23.06.20 25.06.20 closed
task 4 01.07.20 04.07.20 at risk
task 5 04.07.20 06.07.20 in progress
I want to highlight the end date when it is passed today's date, but only if the task status is not Completed or closed.
Many Thanks
Becki
Hello Becki!
Use this formula for conditional formatting
=IF(AND($D1 < TODAY(),$H1 <> "closed",$H1 <> "completed"),TRUE,FALSE)
I hope my advice will help you solve your task.
HI Alexander, If there more to the formula than posted?
Thanks
Becki
Hello Becki!
Use this formula for conditional formatting
=IF(AND($D1 < TODAY(),$H1 <> "closed",$H1 <> "completed"),TRUE,FALSE)
I hope my advice will help you solve your task.
HI Alexander,
Is there more formula?
Many Thanks
Becki
Hello,
I am a case manager and complete needs assessments on a certain example 6/22/2020. I then have to bill on that date each month. How would I create a formula to become red 5 Days before that date each month. As I would want that cell to become red on 7/17/2020, 8/17/2020 and 9/17/2020. But then be a normal color the rest of the month? Does this make sense. Any assistance would be helpful. Thanks- Stacey
Hello Stacey!
If I understand your task correctly, to make the cell turn red every 17th day of the month, use the formula in the conditional formatting rule
=IF(DAY(TODAY())=17,TRUE,FALSE)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hello Alexander,
i have that has column C as start date and column D as end date. i want to highlight the cells from G12 to AD12 which is a two year span but divided in months.
so based on the day i input in column C start, and D end. it highlights the cell under the month. Note, i am not using days in the highlight section, only months. but using days in C and D.
i used this formula for days only, but i need days to work with months.
=AND($G$10>=$C12, $G$10<=$D12)
C12 and D12 is where i input the start and end dates (days) G10 to AD10 is where the months are located (01-January-2020, 01-February-2020, 01-March-2020, etc.)
Appreciate the support and thank you in advance.
Hello!
I’m sorry but your task is not entirely clear to me. Поясните подробнее, что означает: "i used this formula for days only, but i need days to work with months."
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
i figured it out, after days of troubleshooting. i made a simple mistake in the code. instead of this =AND($G$10>=$C12, $G$10=$C12, G$10<=$D12)
and now it works the way i wanted it.
Hello, I have a spreadsheet with forklift certificate training and expiry dates in two adjacent columns. The first column is the date of the training and the second column is the date that the training needs to be refreshed. I would like the second column to highlight 30 days before the date in the cell, every date in this column is different as no two training sessions occur on the same day, so I have 22 rows of different dates in the second column, it is these I would like to highlight 30 days before the date I am an excel beginner so any help would be greatly appreciated
Hello John!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Explain: "I would like the second column to highlight 30 days before the date in the cell". Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Hi Alexander, thanks for the reply,
so i have two columns with 22 rows and each row has a different date in it, the date in the first column is when the training occurred,
the second column has the dates that the training expires, so in column 2 it is the same date as column one + three years (training is valid for three years)
e.g 1st column has 01/02/19 in the cell
and the second column has 01/02/22
What i'd like to have is the date in the second column to highlight one month before the date shown e.g on 01/01.22 i'd like the cell in column two to turn red.
Basically id like the dates in column two to turn red one month before the dates in the cells to remind me that the date of expiry of the training is approaching!
I hope that makes more sense!
Thank you
Hello John!
I hope you have studied the recommendations in the above tutorial. You'll need to set the conditional formatting rule for column B with the following formula applied:
=AND(TODAY() > EDATE(B1,-1),TODAY()<B1)
And choose red as a color to fill the cell with.
I hope this is exactly what you need.
Hi guys,
Could you please help with conditional formation for a day occuring (for example 26th) of each month to be highlighted. Well appreciated.
Also if some one knows how to sort date in pivot table for the Invoice period from 26th of each month. As everymonth is a different number of days, I can't use the period with fixed number of days. Kind of desparate to complete my task.
Eddy
Hello Eddy!
If I understand your task correctly,use a formula for conditional formatting
=DAY($B2)=26
B2 - the first cell of the date range to be highlighted.
I need a spreadsheet to highlight cells in column c if the cell is greater than 16 and if column a is a Monday, Wednesday or Friday
Hello Melanie!
If I understand correctly, you can use the formula for conditional formatting
=IF(AND(C1 > 16,OR(A1="Monday",A1="Friday",A1="Wednesday")),TRUE,FALSE)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi - I have 2 date columns (E baseline date) (F revised date) how can I set formatting that if (F) is equal to (E) green; if (F) is 15 days over then amber; if (F) is 30 days over then red;
I have used 'AND' and 'IF' formula and I get partly cells in (F) green and nothing else.
Help please!
Thank you
Sara.
Hello Sara!
You need to create a separate rule for each condition. I recommend that you study the instructions on the order in which conditional formatting rules are followed.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Please help! I have a sheet where a particular item expires every 2 year. What I'm trying to do is have the conditional formatting come into effect when the 23 month marks hits; and I have no idea if this is even possible. The original date is in the format dd-mmm-yy and is starts in the F4 spot. Any help would be much appreciated. Thanks!
Hello Jeff!
Please go to Conditional Formatting- > New Rule -> Use a formula to determine which cells to format and set the following formula for column A:
=EOMONTH($A1,23) > TODAY()
Hope this is what you need.
Thank you, I was able to combine and redefine your formulas! You made my life much easier.
I would appreciate it if someone can assist me with a date formula :
I have 2 cells A1 and A5
In cell A1 is todays date then Cell A5 should shows current month and Year if todays date is between the 1st and the 15 of current month but if todays date is between the 16 and end of month Cell A5 should change to next month and related year.
For instance
A1 20/04/2020 then A5 should be
May20
If A1 14/04/2020 then A5 should be Apr20
Any help would be highly appreciated
Leon
Hello Leon!
If I understand your task correctly, the following formula in cell A5 should work for you:
=IF(DAY(A1)<=15,A1,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
If it is necessary to change the date format without changing the date itself, Custom Format in A5 should be used. Please go to Format Cells, choose Number -> Custom Format and set "mmmyy"
I hope this will help, otherwise please do not hesitate to contact me anytime.
i have a table for security report with date 1 as starting date and the whole month is triggered by change of month. i used date value and EOMONTH FORMULAS TO ESTABLISH THE CALENDAR. So each date relies on previous date because there is a formula. above the cells is names of days from Monday to Sunday which changes when i change the month because the boxes have formulas too but i format them to "ddd" only to show day name. for this am unable to highlight weekends with conditional formatting for "sun" & "sat". how can i make it.
Hello Peter!
Please check out the second paragraph of this article https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-dates/#highlight-weekends
I hope this will help, otherwise please do not hesitate to contact me anytime.
I need help. I have a start date in column B and end date in column C. Column E to P are the months of the year. I want column E to P to reflect the start and end date with colour blue, for eg if i hv January in column B, and June in column C, then only columns E to J turn blue. How should I do it? Am totally new with Excel!
Hello Flora!
You may use Conditional Formatting- > New Rule -> Use a formula to determine which cells to format and set the following formulas:
For column E:
=$C1 > DATE(2020,1,1)
For column F:
=$C1 > DATE(2020,2,1)
For column G
=$C1 > DATE(2020,3,1)
etc.
I have a spreadsheet with a column that is a date format. I am needing a way to set a conditional rule for invalid dates ie 09/31/2020 to highlight the cell red.
With this I need it to trigger if the data is copied and pasted from one sheet to another or a way once I have all the data in the new sheet that I can run a macro to trigger all the rules on all the cells.
I have an annual training spread sheet and want the cells to change colour 30 days before its due to expire. If there are 100 staff all with different dates they undertook their draining, do I have to apply the formular separately for each one?? I am struggling with the correct formula for one years time.
Hi everyone, I have an item with list of different expiration dates and need my spreadsheet to highlight which ones to choose next based on which one will expire will expire first. Any ideas?
I'm looking to format page 1 of a 2 page spreadsheet. On page 2 is a range of shift times, but I need page 1 to show, via color code of the shifts on a particular day. So should the spreadsheet be open on May 19th then data shown on page 1 as to what shift (via the color code) everyone is working on that date. I hope someone can help
Hi,
I have a workbook with list of supplies of items with different future expiration dates.
I want to put a condition on the Exp.date cell, to alert me with a different color at 30 days from expiration and then again with a different color with just 7 days of expiration. (Goal to remove/then re-order expired supplies)
I need help with the formulas. (I'm able to do it from "today" date but not from future date, for example, if item expire 3 days from today, then the today formula does not work)I will appreciate any help.
Hello everyone, i need a help here. Need a formula that will turn cell red based on TODAY and other date cell, for example B2=RED if TODAY=>E2+4. And to ignore this formula if cell K2 has any value or text or anything...
Thanks
I am trying to format a cell to where it will turn yellow when it reaches a certain month. the situation is where when someone's birth month is within 3 months of the current month, the cell will turn yellow.
Hi
I would like to know the formula for highlighting all times (Say in column A) that are later that the time in Cell B2. Is there a formula for this, as conditional formatting greater than does not seem to work.
IE:Time in Column A1 6:30AM, A2 6:45AM, A3 7:15AM, B1: 6:35AM. Now highlight A2 & A3 Only
Hi,
please help me. I have a excel wherenin i want that data in column 1 in sheet one gets copied to coulmn 1 of sheet 2, once date changes, and data in column 1 should be deleted automatically in column 1 of sheet 1. This should happen after every date change
Hello! I have a spreadsheet that lists upcoming dates for months in advance. There is a conditional format that highlights the weeks in different colors and it works perfectly. However with the new year approaching, the weeks after December 30th are not highlighting. Is there a new formula to correct this?
My current formula is: =WEEKNUM($C3,1)=(WEEKNUM(TODAY(),1)+1)
=WEEKNUM($C3,1)=(WEEKNUM(TODAY(),1)+2)
=WEEKNUM($C3,1)=(WEEKNUM(TODAY(),1)+3)
Etc.
Thank you!
I am using Workday formula whenever I put date in say A1 cell and it will be getting Calculated with workday formula in say B1 formula. At the same time when I set formula for entire B column date will automatically get calculated after putting in respective A column. But when A column is blank say A2 is blank at that time B2 should also show blank; instated B2 is showing me value (i.e 6-Jan-00). Please guide on this.
Thanks,
Deepali
The title of this article is: "How to conditionally format dates and time in Excel" yet there is no information about formatting time, only dates.
I want to format cells which contain a time (eg - 00:05:22). Is this possible?
Thanks
how can i create sheets highlighting red color in dun amount
Please help me how to create when due amount received cell color change
Please help (what am I missing)?
I have a spreadsheet to track my time. I am trying to highlight a column based upon the current day.
My dates cover 2 weeks and go across with the hours worked listed in cells below. My dates are formatted "dd" (i.e. for 11/5/2019 is shows 05). The dates are in row 2.
I used conditional formatting ("use a formula to determine..." ) with the following formula: (I have selected E2:R33 area) E$2=today() and then I have selected the format of a yellow fill color.
It is not working and I cannot figure out why? What am I missing or doing incorrectly? (NOTE: I have tried reformatting the date and even entering in today's date and still does not seem to work.)T.I.A.
Try $E2-Today()=0
I am having an issue that I cannot find the answer for:
I am formatting my cells to display color simply by TODAY() date (Red for ).
The problem is, it isnt changing colors with the date. IF it does change color, the date is wrong (itll highlight RED when the date is clearly several months in the future or vise versa).
Date Calculation is set to Automatic, Number drop down is on GENERAL. It just wont do anything at all. Current format is mm/dd/yyyy.
Can somebody ANYBODY, tell me what I am doing wrong?!
Hi jon, did you receive a response or discover a solution to your problem? I am also having the same issues.
Fill cell C with color if the date is 30 days later than cell A:
Cell A = 03/01/16
Cell C = 05/02/16
The conditional formatting would make Cell C fill with orange.
Everything I find is based on today's date and not two dates that can be at any time. It would be great if I can also fill the cell with a different color if within 30 days.
Hello,
I am struggling to set up conditional formatting for a specific time frame
for example Green if between the times 8:00:00 AM and 8:00:00 PM. I have tried many things but cant seem to get it to work. Any thoughts?? I am having to use Google sheets at work so usually we can't do as much as you can with Excel, but hoping someone knows how. Thank you!
I have a spreadsheet with a Expired due dates in one column . I'm trying to get the cells to turn yellow when its 60 days before the due date , and turn red when its expired .
My expired due dates are in column G .
Name Course Expired /Due date
Jane Doe Annual Training 26/09/2019
John Doe Orientation 01/02/2020
Hoping you can help me. Thanks in advance .
Dear all,
Hope my all friend everything, but i am not ok, we are facing in problem in excel file format issue, please help me about this, Problem: one cell have amount date wise color change issue, how can it's possible, plz help me friend,,,
Kamal
From Bangladesh.
Hi, am have created a drop down list in D to prioritise tasks from 1-4. I would like column E to auto update the due/target date for each line task based on a 30, 60, 120 and 240 day due date form date created.
Is this possible?
Is there a way to copy conditional format to each row.
I have a spreadsheet which shows date someone last attended I wanted it to go one colour if the date is between 180-269 days, highlight a different colour if date is 270-364 and a different colour if more than 365
I managed to do it on one line but when I tried copying it it used the data from first line
Copy the cell with the conditional formatting you want and use the Format Painter to paste the format to the cells where you need the conditional formatting.
If it's a lot of cells, select the block of cells and then click manage rules in the conditional formatting tab.
Make sure it's set at "for this worksheet" and not "current selection".
Click the up arrow next to the conditional formatting you want to apply and select the cells in the worksheet in which you want the conditional formatting to appear. Click apply and ok.
Help on formulas to highlight future dates in calendar.
Have a 12 month booking calendar Coloums ( A:AG) with separate Arrival And Departure date input cells, say coloum ( AJ : AK ) what i would like is, as i enter arrival and departure dates ,EG. enter (arrival date (7-03-2020) departure date (21-03-2020).) reverent cells between dates in calendar to change colour
Thanks for looking.
hi. i have to get a "Icon Set" for the below given information.
there is 3 cells with date which is "PO Issued Date", "PO Delivery Date" & "Delivered Date".
1. i need green color on "Delivered Date" if the Date is entered.
2. i need yellow color if the date is not exceed on "PO Delivery Date"
3. i need Red Color if the "Delivered date" is not filled when the date exceed the "PO Delivery Date".
PO Issued Date PO Delivery Date Delivered Date
7/20/2019 7/26/2019
I cannot perfect R/A/G conditional formatting when using 2 dates. A deadline date and an actual date of completion.
Green if date of completion is on or before deadline date
Amber if date of completion is blank up until deadline date
Red if deadline date passes or date of completion is after deadline date
Any tips?
=TIME(HOUR(M18),MINUTE(M18)+5,SECOND(0)) I am using this formula to show the incremental time of 5 minutes in each column(left to right)to have a color through conditional formatting...till the time slot given for 24 hours(split in five minutes each)its coming perfectly. however when the calculation go beyond 24 hours it is not functioning properly. hence pl advice how add days to this formula
I have two columns. The first contains dates. The second contains $ value. I choose a cell in the worksheet and apply formulae. =if(first cell: last cell="today()",(first cell: last cell="$value). What I am seeking to do is apply the $ value that is opposite the date for today().