DATETIME_DIFF calculation issue

[Elizabeth_Aucti]

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?

?

[momentsgoneby80]

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.

[Elizabeth_Aucti]

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

"?

[momentsgoneby80 · Accepted solution]

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"
					)
				)
			)
		)
	)
)

[Elizabeth_Aucti]

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