Formulas for dates

[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.

[Jeremy_Oglesby]

Hi @Amy_Asch,
You can just pass the result of your first formula into the second formula –

DATETIME_FORMAT(
   WORKDAY(FirstPerfor, -2),
   'MM/DD/YY-ddd'
)

As far as drafting formulas with a text editor goes, using a text editor geared towards writing code is going to be the most useful, because it will auto-create closing parentheses, brackets, quotes, etc for you, and will auto-indent nested lines for you. There are several good options out there for lightweight coding text editors, so just do a google search to find one. I use Visual Studio Code myself, but it’s not the most lightweight option.

After you’ve chosen and installed your editor, simply create a document (usually ctrl/cmd+n while in the code editor) and you can start typing your formulas. Sometimes, making the most of auto-formatting will require saving your file with a language extension (a coding language) – the extension tells the code editor to treat the text in the file as code in the language the extension specifies. Try either .cs or .py – those are the two I’ve found most amenable to writing Airtable formulas, personally. So if you file was named new_file.txt to start, just rename it to remove the .txt extension and add .cs. or .py – so your file will now have the name new_file.py.

Now you can just write your formulas in that file, and then copy paste them into Airtable.

[Amy_Asch]

Thank you so much @Jeremy_Oglesby. The formula within a formula worked perfectly. I’m going to look into text editors. Thanks for the tip about .cs or .py!

[zieddaly1]

Hi

first of all thank you for your help.
using your formula I noticed a small error it is Thursday 02/22/2024 so normally Saturday 02/24/2024 and Sunday 02/25/2024 should be displayed as this week not next week
I think this problem is just for Saturday and Sunday.
the week normally starts from Monday to Sunday