IF Date Formula

[broncos347]

I am trying to work on a spreadsheet where I have a column (col L) with a date in it and I am trying to use this to work out the current status of when something is due, my idea is to do something like this:

if L2<today() then "Overdue"

if L2<today()+7 then "within 7 days"

if L2<today()+30 then "within 3 months"

if L2<today()+180 then "within 6 months"

if L2<today()+365 then "within 12 months"

otherwise "longer than 12 months"

I have the following formula and it seems to work for up to the "within 3 months" but after that it just returns FALSE

=IF((L2<TODAY()),"Overdue",IF((L2<TODAY()+7),"within 7 days",IF((L2<TODAY()+30),"within 3 months",IF((L2<TODAY()+180)<"within 6 months",IF((L2<TODAY()+365),"within 12 months","longer than 12 months")))))

I am obviously missing something but I can't seem to work out what that is, any help would be appreciated.

 

[VBE313]

Do you have Office 365 or Office 2019?

 

[broncos347]

I have an Office 365ProPlus subscription

 

[Marcílio_Lobão]

Broncos347, Good morning.

Maybe a little mistake: Change < by , at "within 6 months"

=IF((L2<TODAY()),"Overdue",IF((L2<TODAY()+7),"within 7 days",IF((L2<TODAY()+30),"within 3 months",IF((L2<TODAY()+180) , "within 6 months",IF((L2<TODAY()+365),"within 12 months","longer than 12 months")))))

Probably exist another solution for you case without using IF function.

Have a nice day

 

[broncos347]

Jeez, that's embarrassing ? should have picked that one up, it works now, many thanks

 

[Marcílio_Lobão]

Broncos347,

Do not worry. It's just a small typo.

Be always welcome.

Glad to have helped.