Formula help

[JoseGarcia]

Hi there, 
need a formula with three sections:
- if targetCloseDate greater than today, 'LATE'

- if targetCloseDate is within 15 days of today, 'WARNING'

- If targetCloseDate is greater than 15 days from today, 'ON TIME'

Using formula below, only first section returns ok values.
any ideas?

if(targetCloseDate<Today(), 'LATE', if(targetCloseDate=Today()-15, 'WARNING', if(targetCloseDate>=Today()-16, 'ON TIME')))

Thanks a million!

[Oleg_Troyansky]

Hi Jose,

Two comments about your logic:

1. Once your first condition renders TRUE, no other conditions matter. Your first condition is supposed to be "GREATER THAN", but the formula shows the "<" sign - is that a typo? All of these dates appear to be "< today()", so no other conditions matter.

2. Check the second and the third conditions too - which one should be "Warning" and which one should be "on time". I sense that there might be some confusion there.

Cheers,

[JoseGarcia]

Oleg, thanks for your reply and support.
Managed to get round it using this long winded formula.
But it works 🙂

if(targetCloseDate<Today(), 'LATE',
if(targetCloseDate=Today(), 'WARNING',
if(targetCloseDate=Today()+2, 'WARNING',
if(targetCloseDate=Today()+3, 'WARNING',
if(targetCloseDate=Today()+4, 'WARNING',
if(targetCloseDate=Today()+5, 'WARNING',
if(targetCloseDate=Today()+6, 'WARNING',
if(targetCloseDate=Today()+7, 'WARNING',
if(targetCloseDate=Today()+8, 'WARNING',
if(targetCloseDate=Today()+9, 'WARNING',
if(targetCloseDate=Today()+10, 'WARNING',
if(targetCloseDate=Today()+11, 'WARNING',
if(targetCloseDate=Today()+12, 'WARNING',
if(targetCloseDate=Today()+13, 'WARNING',
if(targetCloseDate=Today()+14, 'WARNING',
if(targetCloseDate=Today()+15, 'WARNING',
if(targetCloseDate>Today(), 'ON TIME'
)))))))))))))))))