Support with IF Function with Dates

[Kyle Hawes]

Hi,

I'm working on a Work Instructions tracking sheet, I've got everything working bar getting the Status to say Requires Update which is dependent on the 'Days To Update' being less than 7. I understand because of how Excel stores and displays dates than it's not as simple as putting IF(H4<7),"Requires Update","") etc. Would anyone be able to advise me what I'd need to put for it to count under 7 days remaining.

The Formula for Status is;

=IF(H4<X, "Requires Update", IF(F4<>"","Updated",IF(E4=1,"Created",IF(A4="","","Identified"))))
(X being what I'm needing to clarify)

The Formula in H4 is;
=IF(F4="","",((F4+365)-TODAY()&" Days"))

F4 is just TODAY

Mini-sheet;

Testing WI Tracker.xlsm
	A	B	C	D	E	F	G	H
3	Work Instruction	Site	Owner	Date Created	Version	Latest Update	Status	Days To Update
4	Scanning	Network	N/A	13/04/2023	1	18/04/2022	Updated	5 Days
5
6
Sheet1

Cell Formulas
Range	Formula
G4	G4	=IF(H4<7, "Requires Update", IF(F4<>"","Updated",IF(E4=1,"Created",IF(A4="","","Identified"))))
H4	H4	=IF(F4="","",((F4+365)-TODAY()&" Days"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4	Other Type	Color scale		NO

 

[Fluff]

Hi & welcome to MrExcel.
In H4 use

=IF(F4="","",((F4+365)-TODAY()))

and then use a custom cell format of

0" Days"

That way the formula in G4 will work.

 

[Kyle Hawes]

Thankyou very much, simple fix, works exactly as needed.

Hi & welcome to MrExcel.
In H4 use

=IF(F4="","",((F4+365)-TODAY()))

and then use a custom cell format of

0" Days"

That way the formula in G4 will work

 

[Fluff]

You're welcome & thanks for the feedback.