# Re: DATETIME_DIFF calculation issue

Solved
1683 3
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hi, I have a field which is a formula field for a due date group. I have 'due tomorrow set to → DATETIME_DIFF({Due},TODAY(),‘days’)=1, “ :yellow_circle: Due Tomorrow”

But screenshot shows ‘due tomorrow’ for things which are actually due 2 days from now (today is 21st), not tomorrow. I assume its a timezone issue? But not sure what I need to change to fix? Or maybe I have done something else wrong?

?

1 Solution

Accepted Solutions
7 - App Architect

Sorry, that was a miss on my part.
Instead of `<7` (less than 7, so 1-6) you use `<=7` (less than or equal to 7, so 1-7)

``````IF({Due}=BLANK(),
"Not Set",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<0,
"🔴 Overdue",
IF(
TODAY()={Due},
"🟢 Due Today",
IF(
DATETIME_DIFF({Due},TODAY(),'days')=1,
"🟡 Due Tomorrow",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<=7,
"🔜 Within 7️⃣ days ",
IF(
DATETIME_DIFF({Due},TODAY(),'days')>7,
"🔜 Upcoming"
)
)
)
)
)
)``````
4 Replies 4
7 - App Architect

Hi @Elizabeth_Auctions!
I copied the code you provided and though I couldn’t replicate your issue exactly, I did run in to another issue (purple rectangle).

If you enter `DATETIME_DIFF(TODAY(),{ Due},'days')` to your formula field, you will see that instead of returning 1 days from now (green rectangle) it actually returns -1 (red rectangle).

You can fix this by changing the order of your conditions
Red rectangle= `DATETIME_DIFF(TODAY(),{ Due},'days')`
Green rectangle= `DATETIME_DIFF({ Due},TODAY(),'days')`

With these adjustments and slight change to the “ :red_circle: Overdue” condition it now works I suspect you want it to.

``````IF({Due}=BLANK(),
"Not Set",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<0,
"🔴 Overdue",
IF(
TODAY()={Due},
"🟢 Due Today",
IF(
DATETIME_DIFF({Due},TODAY(),'days')=1,
"🟡 Due Tomorrow",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<7,
"🔜 Within 7️⃣ days ",
IF(
DATETIME_DIFF({Due},TODAY(),'days')>7,
"🔜 Upcoming"
)
)
)
)
)
)
``````

Hope that helps.

6 - Interface Innovator

thank you so much. This appeared to clean it up better. Only issue is if the date is exactly 7 days away, the field is blank. How do I make = or < 7 days show " :soon: Within :seven: days

"?

7 - App Architect

Sorry, that was a miss on my part.
Instead of `<7` (less than 7, so 1-6) you use `<=7` (less than or equal to 7, so 1-7)

``````IF({Due}=BLANK(),
"Not Set",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<0,
"🔴 Overdue",
IF(
TODAY()={Due},
"🟢 Due Today",
IF(
DATETIME_DIFF({Due},TODAY(),'days')=1,
"🟡 Due Tomorrow",
IF(
DATETIME_DIFF({Due},TODAY(),'days')<=7,
"🔜 Within 7️⃣ days ",
IF(
DATETIME_DIFF({Due},TODAY(),'days')>7,
"🔜 Upcoming"
)
)
)
)
)
)``````
6 - Interface Innovator

Awesome. I really really appreciate your help. Have a great day