Working with Dates

[Tomo]

I am stuck i want to get a field to show ok if it is less then a year
reminder if it is more then a year but less then 13 months
overdue if it is over 13 months i have this formula but it is not right anyone help please

=IF(A2="Owner","OK",IF(A2="Not required","Not Required",IF(A2="Required","",IF(TODAY()-A2<305,"OK",IF(TODAY()-A2<335,"30 days",IF(TODAY()-A2<365,"60 Days","Overdue"))))))

where is it going wrong excel 2003 by the way

Cheers

 

[rconverse]

What's the problem you're running into?

=IF(A2="Owner","OK",IF(A2="Not required","Not Required",IF(A2="Required","",IF(TODAY()-A2<305,"OK",IF(TODAY()-A2<335,"30 days",IF(TODAY()-A2<365,"60 Days","Overdue"))))))

This is saying that if today()-A2 is less than 305 days, it's okay. You may want to change that to 30, the next one to 60, etc...

Unless, it's really only 30 days late if it's 305 days from today.

 

[Tomo]

Want it to say "ok" if less then 365 days from today
Want it to say "reminder" if more then 365 but less then 395
Want it to say "overdue" if greater then 395


does this make sence

 

[rconverse]

Makes sense.

=IF(A2="Owner","OK",IF(A2="Not required","Not Required",IF(A2="Required","",IF(TODAY()-A2<365,"OK",IF(TODAY()-A2<395,"30 days",IF(TODAY()-A2<425,"60 Days","Overdue"))))))

 

[Tomo]

Nearly there it is confusing upto 60 days from to day should say "60 days"
anything greater should say "overdue"

it needs to be way over the 60 days before the "overdue" kicks in

 

[rconverse]

=IF(A2="Owner","OK",IF(A2="Not required","Not Required",IF(A2="Required","",IF(TODAY()-A2<365,"OK",IF(TODAY()-A2<395,"30 days",IF(TODAY()-A2<500,"60 Days","Overdue"))))))

You can just change the bolded to whatever number of days you'd like before overdue kicks in.

 

[Tomo]

Thanks will use this one