Dates and Formulas

[Holli_Younger]

I’ve searched, googled and tried all the formulas I’ve found and nothing is working. I feel like I’ve spent more time working on these dang formulas than the table itself. Any help is greatly appreciated. Also, if anyone knows anywhere I can test a formula or understand what each means in the order it should be would be a huge help. I just don’t understand Airtables examples (its just words…no way of linking) which is where I think I am failing.

I have a scheduled tab with dates (no times) for upcoming projects. I’d like to have a formula that shows if the date is today - the formula reads - DUE TODAY.
if the date is within a week - Due next week
If its due in 1 day - due tomorrow
If its past due - Past due.

I know its simple, but again - I loose it on the commas, parentheses and quotations. Maybe if i can figure this out with some help I can add some emojis.

Thank you.

[AlliAlosa]

The below should work for what you need!

IF(IS_SAME(TODAY(), Date), "‼️ DUE TODAY", IF(DATETIME_DIFF(TODAY(), Date, 'days') = 1, "⏳ Due Tomorrow", IF(DATETIME_DIFF(TODAY(), Date, 'days') < 7, "🗓 Due Next Week", IF(IS_AFTER(TODAY(), Date), "😫 Past Due")))) 

It certainly takes a little bit to get the hang of writing formulas! One big rule to remember is that the formula will stop after a condition is matched, so it’s important to order things in a certain way. For example, if this part of the formula:

IF(DATETIME_DIFF(TODAY(), Date, 'days') < 7, "🗓 Due Next Week",

Was written before this part:

IF(DATETIME_DIFF(TODAY(), Date, 'days') = 1, "⏳ Due Tomorrow",

The formula would not work correctly. For example… If the number of days until the due date were actually 1, we would expect the result to be, “ :hourglass_flowing_sand: Due Tomorrow”. BUT if the formula was written with those parts switched, the result would have incorrectly been, “ :spiral_calendar: Due Next Week”, This is because the first condition of the formula it matched was that the number is less than 7.

I hope that makes sense!

[Holli_Younger]

Thank you Neads - I notice from other examples my field name has to go into {brackets} so with my table my field name is scheduled date - I don’t see any brackets in your example. I tried using it as is and I get an error message.

[AlliAlosa]

Hi Holli - apologies for the confusion. You need the brackets if the field name is more than one word or has any special characters. You will want the brackets for the field name {Scheduled Date}, while if the field name was just “Date”, you wouldn’t need them.

[Holli_Younger]

Excellent explanation and help. I really appreciate it. This makes much better sense to me!

[Holli_Younger]

Ok I’ve tried with everything I know but still not getting this right. Based on your example and the change of the ‘date’ wording to match my cells - its showing due for tomorrow for yesterday’s date? How can I fix this? What about dates that are scheduled further in advance? I tried a formula for those and they all turn past due…?

[AlliAlosa]

Can you share the formula you are using?

[Holli_Younger]

IF(IS_SAME(TODAY(), {Scheduled Date}), “ :fire: Scheduled TODAY”, IF(DATETIME_DIFF(TODAY(), {Scheduled Date}, ‘days’) = 1, “ :hourglass_flowing_sand: Scheduled for TOMORROW”, IF(DATETIME_DIFF(TODAY(), {Scheduled Date}, ‘days’) =7, “ :stopwatch: NEXT WEEK”, IF(IS_AFTER(TODAY(), {Scheduled Date}), “ :tired_face: Past Due”))))

[AlliAlosa]

Just one tiny correction - change “=7” to “<= 7”. I think that should fix it!

[Holli_Younger]

I fixed the <=7 and I’m still having the same issue. It seems dates scheduled on the 4th are scheduled for next week - when they are actually past due (marked done by checkmark)
Here is the formula I’m using https://imgur.com/LIlQbMK

IF(IS_SAME(TODAY(), {Scheduled Date}), "🚚 Scheduled TODAY 🚚", IF(DATETIME_DIFF(TODAY(), {Scheduled Date}, 'days') = 1, "⏳ Scheduled for TOMORROW ⏳", IF(DATETIME_DIFF(TODAY(), {Scheduled Date}, 'days') <=7, "👀 NEXT WEEK 👀", IF(IS_AFTER(TODAY(), {Scheduled Date}), "😫 Past Due 😫"))))```