# Help with greater than days after date formula

Topic Labels: Formulas
2283 5
cancel
Showing results for
Search instead for
Did you mean:
4 - Data Explorer

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 5
18 - Pluto

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.

4 - Data Explorer

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?

18 - Pluto

You could just change it to this:

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

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?

18 - Pluto

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.