Skip to main content

Help with greater than days after date formula

  • October 14, 2020
  • 5 replies
  • 55 views
R

Hi,

I have a formula I am trying to get to work that I took from another forum post and it’s not working, not sure where the syntax is broken.

I am trying to mark a single select status field good or bad if the date of a last modified field is great than a certain number of days.

IE. If the date of the field “Date of last check” is older than 7 days, change the status field to Bad

Can someone tell me what I’m missing?

IF(
AND(
DATETIME_DIFF(
TODAY(),
{{Date of last check}},
‘days’
) >= 7,
{Status} = “Bad”
),
“Good”
)

5 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • October 14, 2020

This is how your formula should look:

IF(
DATETIME_DIFF(TODAY(),{Date of last check},'days') >= 7,
"Bad","Good"
)

Also, you can’t change another field with a formula. You will get “Bad” or “Good” as the result of this formula in the formula field itself.

If you need to change other fields, then you will need to use automations.

R
  • Robert_Granholm
  • Author
  • New Participant
  • October 15, 2020

Wow, awesome, thanks ScottWorld!

Is there a way to have the field be blank if there’s no date yet listed in the Date of last check field?

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • October 15, 2020

Wow, awesome, thanks ScottWorld!

Is there a way to have the field be blank if there’s no date yet listed in the Date of last check field?


You could just change it to this:

IF(
{Date of last check},
IF(
DATETIME_DIFF(TODAY(),{Date of last check},'days') >= 7,
"Bad","Good"
))
R
  • Robert_Granholm
  • Author
  • New Participant
  • October 15, 2020

Thanks again, I have one more tweak if I am thinking long term. You’ve been a saint with this help, thanks!

There is a field for frequency, daily, monthly, weekly, quarterly, yearly.

Is there a way to adapt this formula so that 7, would be variable depending on what is in the frequency field?

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • October 15, 2020

Thanks again, I have one more tweak if I am thinking long term. You’ve been a saint with this help, thanks!

There is a field for frequency, daily, monthly, weekly, quarterly, yearly.

Is there a way to adapt this formula so that 7, would be variable depending on what is in the frequency field?


You could replace the number 7 with a Switch function like this:

SWITCH(
{Frequency},
"Daily",1,
"Monthly",30,
"Weekly",7,
"Quarterly",120,
"Yearly",365
 )

This would count days, since you specified ‘days’ as the unit specifier in the formula. The formula would be more complex if you wanted to switch the unit specifier to something different besides ‘days’ — you could potentially add another switch statement for the unit specifier.

Terms & ConditionsAccessibility statement