Calculating Status Update based on date

Hi there, I’m hoping someone may be able to help with a formula to update a status, based on the date being entered in another field

I’m going along the lines of:
IF:
DATETIME_DIFF({Date Cleared},NOW(),‘days’)<30,“:open_book: Recently Closed”

DATETIME_DIFF({Date Cleared},NOW(),‘days’)>30),“:closed_book: Closed”

:exclamation: Open”

Thank you

If {Date Cleared} is empty, your formula is probably generating NaN as a result. Wrap the whole thing in an IF({Date Cleared}, …, “Open”).

Secondly, I think DATETIME_DIFF is generating a negative number for you where you assume it’s positive. I’m surprised you’re not getting “Recently Closed” for all rows with a {Date Cleared}. I could be wrong about this though.

Just my guesses.

Thank you. However I still can’t get this to work. The formula I’m using is now:

IF(OR(DATETIME_DIFF({Date Cleared},NOW(),‘days’)<30,“:open_book: Recently Closed”,DATETIME_DIFF({Date Cleared},NOW(),‘days’)>30),“:closed_book: Closed”,“:exclamation: Open”)

But everything is showing as “Closed” … any ideas? :woman_shrugging:

I don’t think you need the “OR”. But first, start small ok? Try part of your formula first to see what values you see. It might surprise you.

DATETIME_DIFF({Date Cleared},NOW(),‘days’)

Then, build up from there. As I said before if you wrap your formula in IF({Date Cleared}, #ALL OF YOUR IF FORMULAS#, “Open”), then it should handle the “Open” rows fine.

Let me know what you find.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.