Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

If Today >= [certain Date In Row], Highlight This Row?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm tracking a bunch of my little projects/tasks via an excel file. It has their "Production Date" as a column, and I was wondering if it would be possible to highlight them in RED or something if it's 90+ after the date listed in Production Date.

So, here's the logic I'm thinking, in pseudocode form: (Assume the row is 1, and Prod Date is Col. A.)

If (TODAY >= A1+90) A.BackgroundHighlight == RED
Yeah it's kinda java pseudo code, but I did it just so you knew exactly what I was trying to do

Is it possible, and if so, how?

View Answers     

Similar Excel Tutorials

Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the

Similar Topics

Column A = Each day's date for this year
Column Q = Each day's production goal.

I am trying to come up with a cell that will look at today's date and sum
the production goal figures between today and the 1st of the month.

I'm thinking I need to summarize the production goals prior to the 1st, then
subtract that figure from the YTD figure.


I can only seem to find info on this where there is a specific date range or they only want to highlight an individual cell.

I currently have this as my formula which highlights the entire row if the date in cell "T" is exactly 14 days from today's date, but not between, i.e. today it would highlight only dates that have 6/30/2011. I need it to highlight the entire row if the date falls between TODAY() and 14 days from today.


I know it's close, just need to know where/what to add to it.



cross link here but not luck on responses yet:

I posted about this yesterday and decided to go the conditional formatting route but it slowed down the file quite noticeably which was already quite slow to start off with hence deciding on the macro route so i've put up a new post.

My aim is to highlight date cells appearing in columns N, P, R, T, V, X, Z, AB either in RED or AMBER/ORANGE if they fall into the below date criteria.

Highlight ORANGE - if the date is within 2 weeks of today's date (including today)
Highlight RED - if the date is within 2 weeks before today's date (excluding today)

14 days <-----HIGHLIGHT RED------>TODAY<-----HIGHLIGHT AMBER------> 14 days

- Headings are in the first row and the dates start from row 2 and shouldn't populate more than a 1000 rows
- Some cells might be blank
- Macro would clear any highlighting it had previously made in these columns and insert new highlighting when re-activated on say a different day.

Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily production
figure for the month by referencing today's date to find which month to

I am using a sumif formula to calculate the month's cumulative production
figure but haven't figured out how to find the highest daily figure of the

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.


Hello everyone, new at the forum. I need a bit of help with a problem that i am running and cant seem to figure it out. I am working on a production schedule for work. I have it set up where a user enters all the info for a new po and i have a column named status it compares the due date to today() and it displays a message if the order is on time or late =if(d2<today(), "on time", if d2>today(),"late")) this function seems to work fine now what i want to do is add another function to the same cell (status)that once i enter a date on the shipped date column it overwrites the function above. I dont know how to proterize funcions while my shipped date is blank or 0 the above function is being executed but as soon as i enter a date i would like the to compare due date with shipped date and dissplay a message instead of the function above that it compares the due date with today()
How do i tell it that while shipped date is blank or 0 compare due date with today() but as soon as i enter a shipped dated compare the due date with shipped date culomns ,
I hope i am making sence lol
Anyone that could help me i would really appreciate it
Thank you

I am using excel as a project list with various tasks listed in rows. One of the columns contains a due date. Is there a way to highlight the date if it is today or earlier?

Hi guys,

I'm looking for an Excel formula to highlight whether a document has been completed within a certain time period and in relation to another date field. Hopefull the screenshot below will clarify what I am after.


- Documents need to be completed no later than four weeks prior to production due date.
- Once a milestone (production or documentation) has been completed, date is listed in relevant 'Complete?' column.

Essentially an IF formulation is required in the 'Status?' column (I6) to reflect the following:

- 'Complete' if G6 features a date
- 'Pending' if G6 is blank but difference between Current Week No (B2) and Production Week Due (A6) is **greater than** 4 weeks.
- 'Overdue' if G6 is blank but difference between Current Week No (B2) and Production Week Due (A6) is **less than** 4 weeks.

I've been playing around with a few formulas but cannot get three results from an IF formula. Is this possible?

If anybody can help me with with the actual formula or point me in the right direction, it would be much appreciated.

Any issues or questions, let me know.


Dear all thank you very much, i am trying to add conditional formatting> The column B had "icon set" if date due, but column A is 'done' or not...i tried to create a sub condition to column "DUE" by taking out the icon if "column A' was YES.
So i had tasks done but with red icon because the date was different from today.

I figure it out that was easier to create CF to column A (i created classic rules Red if no, green if yes)
but i still wanted to highlight the 'not done' tasks (column A) that is already due, date<today in (column B)

I tried If function but i dont know, maybe to formatting without the preset CF i may need a Macro? Can anyone please help me!!!??
please see attached sample and forgive the mistaken REF to the columns letters(i tried to use A, B to become simple but thy are really B and D)

Hello. I have an issue, I am making a spreadsheet for tracking a 6 month date period. every row will most likely have a different date. I want the 6 month expiration to turn the row red.
EXAMPLE: if the date column was dated 1/1/2013 it would turn the row red on 6/1/2013

I can get it to work for a month, but not a 6 month date... any ideas?

If I use condition Formatting this rule works, but the TODAY switch is not what I want to use. need a date 6 months from the start date: =$L$2>TODAY()


I have a column of dates that needs to be compared current date and
highlighted if the date is less that today's date.

The dates were populated in a general non-specific format (below) and vary
in length. My plan was to create a macro, that uses conditional formatting
to say; if the cell date is less than today's date highlight it yellow. I
am not finding this to be an easy task! Anyhow, after creating the
conditional formatting; I apply it the column by using a past special, then
lastly apply a format to the entire column of dates as DATE "*3/14/2001".
What happens really doesn't work like expected. The date reverses itself in
the format of 05/31/2006 without applying any highlight for the conditional
format. Does anyone know how, I can get this to work? I don't mind the
date being reversed, but it's not being recognized or highlighted from the
conditional formatting. Help!

The column below should have two dates highlighted in yellow (second and





VBA Noob:

I was reading another thread where you helped someone with this code.


Say date in A1 enter this in B1

=IF(A1=DATE(YEAR(A1),MONTH(TODAY()),DAY(TODAY())), "<<< Birthday today","")

Will display <<< Birthday today if true

for the conditional formating select col and enter


VBA Noob

I tried it and it works great but,

I would like to do the same thing only have all the birthdays highlight for the
current month and then copy all the highlighted names and birthdays to a list to be printed and posted on a bulletin board.

What changes would I need to make to the code to make this work?



I have six nested IFs as follows:

=IF(AND(AND(DATE(2006,7,21)>=TODAY(),DATE(2006,7,10)<=TODAY()),T7="E"),SUMPRODUCT(--(K7:S7<>"E"),--(K7:S7<>"OC"),--(K7:S7<>"V"),--(K7:S7<>""),K7:S7),IF(AND(AND(DATE(2006,7,28)>=TODAY(),DATE(2006,7,17)<=TODAY()),Y7="E"),SUMPRODUCT(--(P7:X7<>"E"),--(P7:X7<>"OC"),--(P7:X7<>"V"),--(P7:X7<>""),P7:X7),IF(AND(AND(DATE(2006,8,4)>=TODAY(),DATE(2006,7,24)<=TODAY()),AD7="E"),SUMPRODUCT(--(U7:AC7<>"E"),--(U7:AC7<>"OC"),--(U7:AC7<>"V"),--(U7:AC7<>""),U7:AC7),IF(AND(AND(DATE(2006,8,11)>=TODAY(),DATE(2006,7,31)<=TODAY()),AI7="E"),SUMPRODU CT(--(Z7:AH7<>"E"),--(Z7:AH7<>"OC"),--(Z7:AH7<>"V"),--(Z7:AH7<>""),Z7:AH7),IF(AND(AND(DATE(2006,8,18)>=TODAY(),DATE(2006,8,7)<=TODAY()),AN7="E"),SUMPRODUC T(--(AE7:AM7<>"E"),--(AE7:AM7<>"OC"),--(AE7:AM7<>"V"),--(AE7:AM7<>""),AE7:AM7),IF(AND(AND(DATE(2006,8,25)>=TODAY(),DATE(2006,8,14)<=TODAY()),AS7="E"),SUMPRO DUCT(--(AJ7:AR7<>"E"),--(AJ7:AR7<>"OC"),--(AJ7:AR7<>"V"),--(AJ7:AR7<>""),AJ7:AR7),0))))))

It's evaluating to "The formula you typed contains an error." If I omit the last IF, it fine, but I can't determine what the error is. Can anyone help?



Please look at the attached excel sheet. I would like to change color of date (or fill cell) date in column F based on following criteria

1. Date falling in the month of October, highlight with Yellow.
2. Date falling in the month of November, highlight with Blue
3. Date falling in the month of Dec, highlight with Red.

Please help me as soon as possible. Thank you so much for your help.

Equipment Tracking Log.xls


I have set up a spreadsheet with a set of dates occurring in one column.

What I would like is for the cell to highlight in yellow if the value is 3-6 months ago more from today's date and then highlight in red if the value is 6 months or more from today's date

Some help would be greatly appreciated.

I'm using office 2013 if that makes a difference


I have a script that is always looking at the same workbook. How can I change this script below to look at the open workbook?

The problem workbook is high lighted in red below. This workbook can be named any month or year. However I was thinking that since this script calls to open another workbook named "Date" how can it tell I want to move it to another open workbook like the one below???

Any Thoughts? Thank You, Mike


ChDir "G:\Production 2007\Old Production"
    workbooks.Open Filename:="G:\Production 2007\Old Production\Date.xls"
    Sheets("Date").Copy After:=workbooks(" Jan 2008.xls ").Sheets(5) 

'Must Change Line to copy to open workbook however workbook date is also open

We are having Planned & Actual Date is our sheet.

We are using Conditional Formatting with the below formulas.

Highlight Expiry date prior to planned date (Green Colour) - =AND(A1-TODAY()>=0,A1-TODAY()<=3)

Highlight Over Due date once the planned date is passed (Purple colour) =AND(A1<TODAY())

Make the cell Red (B1) if the Actual date is greater than the planned date =$B1>$A1

It is working sucessfully without any problem.

We want to add the below functionality.

1. If the Actual date is equal,less OR Greater to the planned date, font of Cell A1 should trun to black without changing/disable the above feature.

Thanks in advance.



I'm attempting to write a macro to highlight date cells in columns N, P, R, T, V, X, Z, AB either in RED or AMBER/ORANGE if they fall into the below criteria.

14 days <-----RED------>TODAY<-----AMBER------> 14 days

Highlight ORANGE - if the dates in any of the above listed columns fall in the 2 week period AFTER & including today's date
Highlight RED - if the dates in the same columns fall in the 2 week period BEFORE & NOT including todays date

I'd also need to failsafe's as some cells might be blank and it would be run on certain days so the macro would need to clear any highlighting it had previously made and insert new highlighting when activated.

Hello all... I have attached a spreadsheet that's sort of like a finances timetable. It shows the ups and downs of a balance throughout the year (including future balances based on projected payments). What I want to do is have a display on the top row that shows what my minimum future balance will be and what date that will occur. I have a cell right now showing the minimum of the column, but I want the range to be only from today forward.

The logic (in my head) would be:

1) find the minimum in column D,

2) compare the date in column A of that same row to today's date and see if it is equal or greater to today,

3) if the date in column A is greater than or equal to today's date, then display the balance and the balance date on the top row,

4) if the date in column A is less than today's date, then find the next lowest minimum balance in column D, repeat the same date check, and continue until the "greater than or equal to today's date" argument is satisfied.

I have no idea if this is possible within the confines of Excel, but if anybody had any ideas or suggestions, they would be most appreciated! Thanks!



I have a table of data that has dates, production lines and production
volumes (table one):

Production Line Date Volume
A02 13/3/06 189
C32 13/3/06 325
T12 14/3/06 21

The table contains data retrieved from the beginning of the year so there is
a substantial amount, I have on a separate sheet a list of further data
(table two):

Production Line Date Tank
AD02 13/3/06 A
CD32 13/3/06 C
TD12 14/3/06 B

I want to use a formula that references two cells ( Production line and date
in the first table) and returns from the second table the tank that the
particular line was flowing into on that date.
You will also notice an additional problem between the two table is that
table one Production Line reads "A02" and table two's Production Line reads
"AD02" so obviously this formula will have to ignore the additional D.

Someone has suggested an array formula but seeing as I am using this formula
possible several thousand times on one sheet it is slowing the whole workbook
down far too much.

Thanks for any help in advance.


Hello friends...

I am trying to write "IF" statement.... as per following logic....

1. Column A - Date of Expiry
2. Column B - NOC issued
3. Column C - Status as today (Status options, "Expired", "NOC" given and "Valid")

Condition 1: If Date is < than Date(today) then in Column C "Expired"
Condition 2: If in Column B "NOC" than in Column C "NOC" ( P.S. irrespective of Date of expiry)
Condition 3: If Date > than Date (today) then in Column C "Valid"
Condition 4: If Date > than Date (today) and in Column B "NOC" then in Column C "NOC"


in above statement only two conditions are working...

Thanks in advance


In my spreadsheet, cell A1 is always todays date =TODAY()
I have every cell in column A (except A2 and A3) to highlight green when their date matches the current date. However, I would like for all cells in that row, B through N to highlight the same color when the date in A matches the date in A1.

I know how to do conditional formatting, but I have not done something like this before. I also want to remove any highlighting when the dates do not match...


I need help with two nested IF statements. Both need to set the time based on certain conditions.

First set

if date < today then set time to today @ 17:00
if date = today AND time < 17:00 then set time to 17:00
if date = today AND time > 17:00 then set time to time.

second set

if date = today and time > 17:00 then set time to time
if date = today + 1 (ie tomorrow) and time < 5:00 then time = time.
if date = today + 1 AND time > 5:00 then time = 5:00
if date > today + 1 then time = 5:00

Using these to set run time goals for a machine by shift based on run start time/date and finish time/date.

Already using =TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm") to combine the time/date cells and using =IF(A2<F2,F2,A2) where f2=now() to set the date to today in the equations in long runs. Just need a way to set the times. I should be able to reverse engineer the statements for the day shift running from 05:00 to 17:00 since I won't need to account for the day change in the middle of a shift.

Thank you in advance.

Hi I am using following code to achieve it. This works fine, however has two issues:
1 It continues to highlight previously highlighted cells .e.g. If today's date is 08 Jul, then it also highlights, o7 , o6... Jul. I want highlight for previous cells to be removed
2. It only highlights column 'A' cells.
I want it to highlight the entire row.
Is it possible??

Please Login or Register  to view this content.


I want to highlight an entire column based on whether a cell in that column is equal to today's date.

I know how to write a loop that cycles through all of the columns until it finds today's date and then modify the column's properties, I was wondering if there was an easier way to do it. Also, I want the highlighting to be temporary meaning it should not save.

Any ideas?


I am looking at an If formula, i.e. if start date (fixed date in a cell (a1)is more then 5 years ago (from today's date), then answer "a", if not, answer be"

I have never written a date formula to know how to do it. Should I be using the DATE function, and looking at the Date result being less then today?

I assume it would be something like =if(a1>???,a,b)

Any assistance would be greatly appreciated