Hello,
I'm trying to highlight rows based on the difference between a date and time within a cell in the row and the current date and time.
(All data is to be calculated during working days, M-F, and working hours, 8-5)
Yellow - Difference between A1 and current date and time is less than or equal to 7 working days
Red - Difference between A1 and current is greater than or equal to 8 working days
Thank you.
Hi, welcome to the forum
I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Book1.xlsx
Attached is the sample book. I would like the conditional formatting to look at the date in column H, subtract is from NOW() and highlight the row based on the following:
Green: <7 business days
Yellow: 7-14 business days
Red: >14 business days
Thank you.
I think you mean TODAY()? NOW() includes the time as well as the date
I think these are the rules you need...
=NETWORKDAYS(H2,TODAY())>14
=NETWORKDAYS(H2,TODAY())>=7
=NETWORKDAYS(H2,TODAY())<7
Thank you. Won't that only change H2? I need it to change the whole row and make that change for the entire sheet.
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =NETWORKDAYS($H2,TODAY())>14
Repeat 3 and 4 for the other 2 rules
Thank you! That was very helpful.
Is there a formula to apply for blank cells so it doesn't apply a color? I have rows 2-500 selected for the formatting, but I only have initial data in the first 5 rows with more to be added as time goes on. The remaining 495 rows are red.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks