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?
?
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.
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
"?
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"
)
)
)
)
)
)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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.