I have a calculated field and can’t seem to get the formula quite right. It’s to calculate if an order is overdue by establishing if the order has been out for greater than 7 days. If the date Received is filled in, it does not have to calculate and displays nothing “”, but if it’s been out over the 7 days, it’s supposed to display “OVERDUE”.
IFNOTEMPTY([RECEIVED];””;(IF((TODAY()-[DATEOUT]>7);”OVERDUE”;””)))
It works if I only use IF((TODAY()-[DATEOUT]>7);”OVERDUE”;””) – but then if the item is RECEIVED, and it’s over the 7 days, it’s still displaying the OVERDUE – and of course it isn’t.
Any suggestions? the square [ ] brackets are only to indicate the fields used.
What is the data type of the DATEOUT field? Also, the TODAY() function will return the number of seconds since January 1, 1960. If DATEOUT is a Date field, then it also returns the number of seconds since January 1, 1960 to the date specified in that field’s value.
So you’re subtracting seconds. You’ll want to convert those units to days instead. Or just use the DAYS() function, which does that for you.
IFNOTEMPTY([RECEIVED]; "" ; IF(DAYS(TODAY(); [DATEOUT]) > 7); "OVERDUE"; ""))
Thanks… that makes sense.. but I must be missing something – Can I assume that spaces don’t make a difference?
IFNOTEMPTY([RECEIVED]; “” ;IF(DAYS(TODAY(); [DATEOUT]) > 7; “OVERDUE”;””))
Didn’t return anything. I closed the doc, reopened, and for some reason, the [RECEIVED] field was filled in with yesterday’s date, which makes sense that OVERDUE isn’t displayed – but why is it filling in? I delete the date, refresh, and it fills in again.
I added a new test record with no RECEIVED date (and more than 7 days after the DATEOUT) but still no OVERDUE.
Do you have the Result Type set to be Text? (sorry, haven’t had a chance to look at your file yet).
.
