Nested If using TODAY

[Poolshark25]

Hi all,

I'm struggling with setting up a sheet to warn me when a date is within 7 days of approaching todays date. All the research i seem to be doing is not quite working for me.

In columns A and B i have 2 dates (A start date in A and an end date in B)

I need to write an IF statement that will return 4 different results as text as follows:-

A: If the end date is more than 7 days prior to todays date (I.E 10 days before todays date) i want to return "Overdue"
B: If the end date is within 7 days of todays date i want to return "Check"
C: If the end date is later than todays date i want to return "OK"
D: If the cells do not have a date in them return ""

Simplified i want to be warned to 'check something' if it is within 7 days of expiring. If it is not ready for expiring yet (More than 7 days left to run on it return "OK". If it has expired to return "Overdue". If the date cells are not populated to return "".

I hope this makes sense and any help with this would be greatly appreciated.

My formula that is not quite working is as follows: =IF(C5=(TODAY))+7,"Check" but that only returns "Check when it is exactly 7 days prior to today, i want it to return "check if it is either 7,6,5,4,3,2, or 1 day prior to todays date. I would then also need to get the the other 3 bits working

Ernie

 

[Fluff]

How about

=IF(C5="","",IF(C5<TODAY()-7,"Overdue",IF(C5<=TODAY(),"Check","Ok")))

 

[Poolshark25]

How about

=IF(C5="","",IF(C5<TODAY()-7,"Overdue",IF(C5<=TODAY(),"Check","Ok")))

Hi Fluff, thanks for the message.

I couldn't get this to work properly.

I have found a formula that works great for the most part but is just missing one piece. I have attached my sheet which shows what i'm trying to achieve and i now just need this adapting slightly so that it returns a blank cell when there are no dates in the cells rather than returning 'Expired' in column D on the Strimmers row. I have many cells which are not yet populated with dates so this is a bit confusing when scanning the sheets looking for expired dates.

Here is my current formula that needs adapting:

=IF(TODAY()>C4,"Expired",IF((C4-TODAY())<=7,"Retrain","Valid"))

If you are able to help with this i would be grateful.

Ernie

 

[Fluff]

I have found a formula that works

But that formula is not doing what you asked for.

i now just need this adapting slightly so that it returns a blank cell when there are no dates in the cells

Just do that the way I did.

 

[Poolshark25]

Hi

I'm not sure i'm explaining what i actually need or maybe i'm approaching it the wrong way.

If todays date is 11/03/2022 and an expiry date is between 05/03/2022 and Todays date (7 days) i want to be warned with 'Check' as it is within a week of expiring

If todays date is 11/03/2022 and an expiry date is earlier than 05/03/2022 (More than 7 days before todays date) i want to return 'Ok' as there is still more than a week before it expires

If the expiry date has surpassed todays date then i want to return 'Overdo'

I will then do conditional formatting to do a 'Traffic Light' system to show the 'Ok' return in GREEN as it is still ok
'Check' in AMBER as it is in danger of expiring soon
'Overdue' in RED as it needs urgent attention

I am ok with the conditional formatting once i get the formula working how i need it

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	C	D
1
2
3
4
5	10/03/2022	Overdue
6	16/03/2022	Check
7	19/03/2022	Ok
8
Data

Cell Formulas
Range	Formula
D5:D8	D5	=IF(C5="","",IF(C5<TODAY(),"Overdue",IF(C5<=TODAY()+7,"Check","Ok")))

 

[Poolshark25]

Hi. Thank you for that. I have logged off for the weekend now so I’ll give it a go on Monday. Have a nice weekend. Ernie

 

[Fluff]

Ok

 

[Poolshark25]

=IF(C5="","",IF(C5<TODAY(),"Overdue",IF(C5<=TODAY()+7,"Check","Ok")))

Hi Fluff

This is working great. Many thanks

Ernie

 

[Fluff]

You're welcome & thanks for the feedback.