Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


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 Video Tutorials

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.

Thanks!



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.

Hi I have an excel spreadsheet (issues tracking log), that contains a column for 'Due Date' and a 'Date Closed' column. I would like to color code the rows to differntiate rows that contain issues that are closed, past due, and coming due, as below:

1. Closed, rows that have a value for 'Date Closed", I want to highlight (color-fill) grey ( grey out).

2, Past Due, rows that do not have a value for "date Closed" and for which the Due Date is in the past, I want to highlight red.

3, Coming Due, Rows whos target date is today or next 3 days, I want to highlight yellow.

I want to highlight the entire row, not just the cell according to the above criteria. Any suggestions?


I am trying to color code a worksheet for project status based on % complete and Due date.

My logic
If Due date is less than 7 days from today and % complete is less than 75 highlight red
If Due Date is less than 14 days from today and % complete is less than 50 highlight yellow
Else highlight green.

Here are the rules I used in conditional formatting:


The first rule works however it never executes the second rule (even if I switch them).
=IF(and($F$9<Today()-7, %G%9<75%)
=IF(and($F%9<Today()-14, %G%9<50%)

I haven't gotten to Else highlight green.

Your help & expertise is greatly appreciated.


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

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

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

Basically in need a MAX version of the above formula.

Thanks!



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?


Good Morning All,

Using Windows XP and Excel XP.

In Column A I have a weekly start date and in column B I have an weekly end
date with other data in columns C to Z.
I want to highlight the two columns when today's date falls within the
weekly date range. I know that the =TODAY() function will only highlight
today's date. How can I write the formula in the condtional format to
highlight the weekly range when a the date falls within that range.

Example:


A B
-----------------------------------------------
1 Jan 1 Jan 7
2 Jan 8 Jan 14
3 Jan 15 Jan 21
4 Jan 22 Jan 28
5 Jan 29 Feb 4
6 Feb 5 Feb 11
7 Feb 12 Feb 18
etc.........

How can I write the conditional format formula that will highlight columns
A6:B6 with todays date (Feb 6)


Thank you in advance,

Michael







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 everybody!
I am a new member on this forum, and I am interested about the way how I can insert the current date (using TODAY() ) in one cell by inserting at list one character, or a specifically type of characters (a number, in my case) into another cell. I want to create a table for monitoring and tracking production for a small production shop. Actualy I want to have a range of cells in my table where operators from different production stations can insert how many parts produced in that day, and after this in another range of cells to have automatically the current date inserted.
Thank You


I am a Production Manager, and I am trying to ramp up our excel program to show where everything is at in the production process. I am creating a drop-down menu with all of the various stages of production.

I need to have one option that states "Needs to Dry Until" and then today’s date, plus 2 days, excluding weekends. And when it hits that date, it will switch to "Needs Assembled".

So for example, my painter finishes painting a product today (9/22), I pull down the drop don menu and select "Needs to Dry Until 9/24". I would need that cell to read that date for as long as it is selected, then on 9/24, it would switch to "Needs Assembled."

If my painter finishes painting a product tomorrow (9/23), I pull down the drop don menu and select "Needs to Dry Until 9/27", because the weekend falls within those 2 days. Then on 9/27, it would switch to "Needs Assembled."

I'm having trouble with 3 things:

1. I cant get the date to show as selected when the text comes before the date using this formula: ="Needs to Dry Until"&TODAY()+2

2. I can't discern a way to make the cell change on the desired date.

3. I can't figure out a way for the cell to take the weekends into account.

Thanks for the help!!!!




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()

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


I've attached the file for a production operating report. (Woops, it exceeded the size. I delete all of the tabs that have the production data to make it smaller, hence the REF errors)

What I'd like it to do is this:

As you see when it opens, there is a barrier log to list barriers each shift encountered the previous night. However, I don't want them to type it in there as we will lose the data every day. I have a barrier log tab where I want the barriers entered so I can track them.

So, if the report date is 6/25 (which is just a TODAY()), that means we are running it for our meeting the next morning. The barriers listed should be a lookup from the barrier tab for TODAY()-1.

A vlookup obviously doesn't work because there would be multiple hits for each date. Any ideas? I have an idea this may be another index/match thing, but I've never been too good at that.

Thanks!


Hi,

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?

Thanks,
Gos-C


Hi all, I'm using code below in a macro to show a popup box if the date is not equal to today. (date in cell g2 of the excel file)

Works on my computer, but for one friends, when he tries to run it, he got an error message "Compile error: Can't find project or library" pointing to Date.

Can someone help on this ?

Code:

Dim warning As Variant
If Range("G2").Value <> Date Then

warning = MsgBox("The date in your file is yesterday" & Chr(13) & "If your data corresponds to today, use button 'Switch date back to today' to change the date before saving", vbExclamation + vbOKOnly, "Yesterday's date")

Exit Sub
End If







Guys,

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

Hello,

First, thanks for having this forum. It is greatly appreciated. I have a gantt style spreadsheet. I track multiple projects with a start date, due date and a complete date. I currently have the formula =AND(J$5>=$E6,J$5<=$F6) that will highlight a date range, from the start to the due date. It is highlighted in yellow. I would like to keep that same date range and have it change to green when the project is completed. I would also like to have the date range change to red if it goes pass the due date.
I was thinking that I need to have a formula that looks at the date completed verses the date due. If the date completed is equal to or less than the date due, change the date range to green. To change to red, it would be the if =today() is greater than the due date, turn the date range to red. I can't figure out how to combine the different pieces into one formula.
I have attached a test spreadsheet with some dummy data. Any ideas or suggestions would be appreicated.
Thanks Tj


Hi,

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



Code:

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
    Windows("Date.xls").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close







Hi,

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



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


Gary


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

P