Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Calculating Status Update based on date

Topic Labels: Formulas
355 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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”

image

Thank you

3 Replies 3

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.