Skip to main content
Solved

DATETIME_DIFF calculation issue

  • March 22, 2021
  • 4 replies
  • 57 views
E
Forum|alt.badge.img+8

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?

?

Best answer by momentsgoneby80

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

"?


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

    M
    Forum|alt.badge.img+14

    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.

    E
    Forum|alt.badge.img+8
    • Elizabeth_Aucti
    • Author
    • Known Participant
    • March 22, 2021

    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.


    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

    "?

    M
    Forum|alt.badge.img+14

    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

    "?


    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"
					)
				)
			)
		)
	)
)
    E
    Justin_Barrett
    E
    Forum|alt.badge.img+8
    • Elizabeth_Aucti
    • Author
    • Known Participant
    • March 22, 2021

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

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

    Terms & ConditionsAccessibility statement