Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...
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 ...
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 ...

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







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.

=INDIRECT("T"&ROW())=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+14)

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

Thanks!

Toni




cross link here but not luck on responses yet:
http://www.mrexcel.com/forum/excel-q...ay-s-date.html

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)

i.e.
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.

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?




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 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
last).





2006/05/31



2004/09/30



2005/07/08



2004/07/31





VBA Noob:

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

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

=A1=DATE(YEAR(A1),MONTH(TODAY()),DAY(TODAY()))

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?

Thanks
Charles


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.

Rajeev




Hello,

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 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"

=IF(1A<TODAY(),"Expired",IF(B1="NOC","NOC",IF(A1<TODAY(),"Expired","Valid")))

in above statement only two conditions are working...

Thanks in advance

Hello.

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...

Thanks




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.


regards

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?


Hello everyone,

I'm hoping someone can help me here. I'm trying to produce a basic action log spreadsheet and I want to add due dates. In the cell alongside due date I want the status to automatically appear with the following logic:
if today's date is less than the due date but not within a week of the due date, then I want it to read "Coming Due"
if today is within a week of the due date I want it to read "Due"
and if today is past the due date, then "Overdue"

So far I have this:

Where A1 is the due date:

=IF(A1="","",IF(TODAY()A13,"Overdue",)))

I'm having trouble allowing for the due within a week part of the formula. Any help would be much appreciated.

Natasha




Hello,

I use Excel 2003 and I am trying to use the three conditional formats to highlight cells in a column dedicatd to date cells only for :- cells containing today's date cells containing yesterdays date cells containing the day before yesterdays date.
For 1 "today's date" I am using "Formula is" +TODAY() which seems to work OK.

However for 2 & 3 I have tried both 'formula is' and 'cell value' without success.

Can anyone tell me the correct way to define 'yesterday' and 'the day before yesterday' in the conditional formatting window please?

Thanks

I have a column which shows the target date for an action to be completed. DD/MM/YYYY

I would like to be able to have the sheet highlight any actions that have over run this target date.

Example
A task to be completed by 02/01/2011.

I open the sheet today 04/01/2011 and the task should be highlighted in red.

Finally
I have a second sheet called Metrics that highlights the number of tasks completed and the different status of each of the open tasks e.g. In Progress: 10 Not Started: 5

I would like to be able to show how many of the tasks are past their target date.


Anyone with any ideas???

Thanks


Hi all,

I'm having trouble creating a formula as follows:
Column A: either Y or N
Coumn B: a forecasted date
Column C: $$ amount of transaction

What I would like to do is create a report that shows the sum of column C
for anything where column A is "Y" and the date in column B is today's date
or up to 90 days after today's date (Today +90)... I'm having problems with
the date criteria, and would appreciate your help! Thanks



Hi,
I need to calculate the difference in Years, Months and Days between:

Date 1 = TODAY()
Date 2 = 4 years after a date in cell A1, which will always be earlier than today's date

(A bit of backround - I have certain risk management procedures that have a lifespan of 4 years. I want to calculate the time between now and 4 years after the date the procedure was completed, essentially to see how long before they have to be redone).

So far I have:

=DATEDIF(A1+4,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

But that returns #NUM!.

Removing the +4 obviously just calculated the difference between the date in A1 and today, but I need the date in A1 PLUS 4 years and today.

I have also tried:

=(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1))-TODAY())/365.25

which works in theory, however:
a) no consideration for leap years
b) does not return nY, nM, nD - only the decimal.

However I would be happy to use this method if I could convert it to Years Months Days.

Any help would be very much appreciated. Thanks.


I need to highlight when data is input on a date previous to today's current
date. To track whether employees are inputting their information when the
work is performed or at a later date. I cannot determine the formula
necessary within excel to complete this task. Any help would be greatly
appreciated.



Hi

Does anyone know how I can create a formula that would highlight the cell in a colour if the date was past todays date?

This is what I'm doing - I have the expiry date of people's insurances. so e.g todays date is 8 th January, so if the insurance had expired 31.12.08, i would want the cell to be highlighted red.

I think it may be an IF function but I cant remember...

thanks


Problem:

The range B7:K7 contains columns of "dates" and "characters" like "A", "B" and "C" . Range D2 = Today's date. If Range C7 is blank then it should calculate Today's date minus Column B7 (i.e D2-B7) so on:

B7 = "01-Apr-2009", C7 = "02-Apr-2009", D=7 = Status as "(A)"
B7 = Submitted Date, C7 = Received Date, D7 = Status
Difference Between Today's Date and "B7" date is required if "C7" is Blank date. Otherwise it should search next group (E, F)

=IF(AND(ISBLANK(C7),NOT(ISBLANK(B7))),D2-B7,IF(AND(ISBLANK(F7),NOT(ISBLANK(E7))),D2-E7,IF(AND(ISBLANK(I7),NOT(ISBLANK(H7))),D2-H7,IF(AND(ISBLANK(L7),NOT(ISBLANK(K7))),D2-K7,"--")))))

-- Excel File is attached.

Is there a simpler way to achieve the same results?

Thanks and regards,
Ananthakrishnan. K.


We have a spreadsheet which we use to track work progress. In it we have a column which lists a date due for projects. By design, a few of the cells are blank. What I would like to do is use conditional formatting to highlight all cells in blue which contain dates within 90 days of today's date to alert our members of the upcoming due date. I'd like to highlight all cells in yellow that are past due by 1 to 60 days. And, I'd like to highlight all cells in red where the due date is delinquent by greater than 60 days. What conditional formatting codes do I use to accomplish these requirements? Thanks in advance.




I am working on a project that will require vists to be scheduled between 2 and 4 times annually i want the cell "Date of last inspection" to reflect the most current date indicated that is <= today regardless if there are blank cells in range. Curently this is what i have:
=IF(R3<=TODAY(),R3,IF(Q3<=TODAY(),Q3,IF(P3<=TODAY(),P3,IF(O3<=TODAY(),O3,"")))) Formula results in "1/0/1900" if there are blanks


Visits/year Date Scheduled Date of last inspection Q1 Q2 Q3 Q4
2 5/11/13 4/25/13 2/15/13 4/25/13 5/11/13 6/11/13
3 5/13/13 1/0/1900 2/11/13 5/13/13
Sample.xls



Good afternoon, i need your help pls, I generate an excell timeline and the purpose is to visualize which task are about to run, or running or finished

There is column name Production Date type date which list the Production Date we are going to start loading in a Production System,

I created 3 conditional rulkes for each c ell so os ok up to know.

The problem is i got stuck to copy a cell value into multiple cells, the formule seems to be OK but inserts data value for everysingle cell in which condition is true, but what i want is to insert Production Date column value in a range multiple cells based in multiple formatting

This is the formula:

=IF(AND(P$4>=$B6;P$4<=$C6);"Production Date: "&TEXT($G6;"dd-mmmm");"")


I attach timeline example, formula is in spanish becuase of language.


TRy to get documented but falied, im newbie, and i am more familiar with formulas but VBA and macros i have no idea, but if is absulutely helpful, i will appreciated your asistance and your help in adavnced

I m using excel 2003.

Irequired formula in column F to calculate as per below :

if column D= IND & date (column E) is before one year then status is "PW" calculate with today system date

if column D= IND & date (column E) is within one year then status is "W" calculate with today system date

if column D= G & date (column E) is within two year then status is "W" calculate with today system date

if column D= G & date (column E) is before two year then status is "PW" calculate with today system date

In addition to above how to genrate auto alfa num numbers.

I attaching file for your ready ref.


He folks,
I am terrible at Access but figured I had better start to learn something...

I have a date field that I have to always enter "today" date every time I create a new entry. There has to be a simple way to tell this db to just enter "today's date"?

Also will it store that date or will that auto date renew as the current date every time the record is opened? I need it to only register the initial date of entry.

Thanks in Advance