Calculating Status Update based on date

[Katie_Byrne]

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

[augmented]

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.

[Katie_Byrne]

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:

[augmented]

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.