Due date "IF" formula Format

[dragonmouse]

I have a spreadsheet that has multiple "due" dates. I would like to highlight either the cell or at least the text to a color IF the maintenance due date is within 7 days of current date. In a nutshell a widget may require different types of maintenance for various frequencies. (most commonly 1, 7, 14, 28, 56,168 and 336). Not all widgets require maintenance for ALL frequencies. Most only require maintenance on one or two of the frequencies. For example a Widget may require a power on test every 14 days, and may require a dust blowing every 168 days.

Today's Date is 21-Jan-2016
Current calculations:
A1: Date Complete (17-JAN-2016)
B1: Maintenance Date Frequency (14)
C1: =IF(M3=14,$H$3+M3," ") If B1 has a frequency listed it will return a maintenance due date of 31-Jan-2016 otherwise if B1 is blank it will return BLANK.

IF the date returned in C1 is 7 days or less than the current date 21-JAN-2016, I want to be able to quickly spot it by a change in text color or cell highlight. and I'd like this change to occur in cell C1.

Any help will be appreciated.

 

[Webbarr]

Hi dragonmouse,

Does your IF statement in cell C1 display the correct value? If so you could use this in your conditional formatting rule to highlight the cells that match the criteria.

Sorry if I've misunderstood, if I've got it wrong/you're unsure what I mean let me know.

 

[jtakw]

Hi,

You can use the first conditional formatting formula (highlight yellow) for what you described, I've included a 2nd conditional formatting formula incase you want the cell also highlighted (red) if the date returned in C1 has past (past due?)

Excel 2010
	A	B	C
1			1/29/2016
2			1/25/2016
3			1/18/2016
Sheet1

=C1-7<=TODAY()

Format Yellow

=C1< TODAY()

Format Red

Put these conditional formatting rules in C1 as you described.

PS: One other thing, your C1 formula =IF(M3=14,$H$3+M3," ") puts a "SPACE", not a "BLANK", in the cell if M3 does not equal 14, which could cause problems with formulas and calculations, you might want to change the formula to: =IF(M3=14,$H$3+M3,"")

 

[dragonmouse]

That appears to be working out swimmingly! THANK YOU!

 

[jtakw]

That appears to be working out swimmingly! THANK YOU!

You're welcome.