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).
I’m late to the game on this one, but maybe the TF5 Assistant can assist. As this AI generated off the TF5 docs, use with care and I hope that it helps:
The issue lies in the way the IFNOTEMPTY() function is being used. Here’s a refined version of the formula that should work as intended:
IFNOTEMPTY([RECEIVED]; “”; IF(TODAY() – [DATEOUT] > 7; “OVERDUE”; “”))
### Explanation:
1. **IFNOTEMPTY([RECEIVED]; ""; ...)**:
– This checks if the RECEIVED field is not empty. If it is not empty, it returns "" (an empty string), ensuring no further calculation occurs.
2. **IF(TODAY() - [DATEOUT] > 7; "OVERDUE"; "")**:
– If the RECEIVED field is empty, the formula calculates whether the difference between today’s date (TODAY()) and the DATEOUT field exceeds 7 days. If so, it displays “OVERDUE”; otherwise, it displays "".
### Key Notes:
– Ensure both [RECEIVED] and [DATEOUT] are properly formatted as date fields in Tap Forms.
– The formula is structured such that it prioritizes checking if the item is received before performing date calculations.
This approach adheres to Tap Forms’ formula syntax as outlined in its documentation, ensuring compatibility and correct evaluation. If you encounter further issues, double-check the field types and ensure they match the required format.
Thank you…. that makes much more sense.
Thank you…. that makes much more sense.
The AI misses an important point in the formula. When you subtract two dates, the result is returned in seconds, not days.
.
