Dates and Formulas

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!

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.

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.

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

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…?

Can you share the formula you are using?

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”))))

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

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 😫"))))```

After a quick play, I found a few problems:

1. The order of the checks makes a difference. The order that I found to work consistently is Past Due first, then Today, then Tomorrow, and finally Next Week (i.e. chronological order)

2. In order for {Scheduled Date} to compare correctly against TODAY(), the latter had to be forced into the correct time zone, which involved turning every TODAY() in your formula into:

   DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "America/Chicago"), "M/DD/YYYY")
   

3. Your DATETIME_DIFF functions had the first two items reversed, leading to negative numbers, so the some comparisons didn’t return the results you expected.

The final modified function that I was able to test successfully for all states is this:

IF(
    IS_AFTER(
        DATETIME_FORMAT(
            SET_TIMEZONE(
                TODAY(),
                "America/Chicago"
            ), "M/DD/YYYY"
        ),
        {Scheduled Date}
    ), "😫 Past Due 😫",
    IF(
        IS_SAME(
            DATETIME_FORMAT(
                SET_TIMEZONE(
                    TODAY(),
                    "America/Chicago"
                ), "M/DD/YYYY"
            ),
            {Scheduled Date}
        ),
        "🚚 Scheduled TODAY 🚚",
        IF(
            DATETIME_DIFF(
                {Scheduled Date},
                DATETIME_FORMAT(
                    SET_TIMEZONE(
                        TODAY(),
                        "America/Chicago"
                    ), "M/DD/YYYY"
                ),
                'days'
            ) = 1,
            "⏳ Scheduled for TOMORROW ⏳",
            IF(
                DATETIME_DIFF(
                    {Scheduled Date},
                    DATETIME_FORMAT(
                        SET_TIMEZONE(
                            TODAY(),
                            "America/Chicago"
                        ), "M/DD/YYYY"
                    ),
                    'days'
                ) <= 7,
                "👀 NEXT WEEK 👀"
            )
        )
    )
)

You’ll need to adjust the timezone setting for your local area, of course.

If you want to condense things a bit, you could make a formula field that just does the TODAY time zone force using that one-liner I listed above (again adjusted for your local time zone), then reference that field wherever it needs to occur. I made such a field called {Today}, which let me shrink the main formula down to this:

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

Sorry for the delay. Things have been busy on this end.

If I’m reading your comments correctly, you want this checkbox to indicate that a task is complete. If checked, all of the other date comparisons would be ignored. If that’s an accurate assessment, this will do the job (I named my checkbox field {Complete}:

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

If you want the field to just remain empty if {Complete} is checked, replace "Done!" with "" (an empty string).

BTW, that checkbox article you linked doesn’t mention that testing the state of a checkbox with the format IF(Checkbox, "A", "B") also works. :slightly_smiling_face:

Ah, I forgot to update my question - I’ve removed the checkbox because it seemed redundant to have a status (single select) and a check box. My single select options show ‘Complete’, ‘In Progress’, ‘Need to reschedule’. I’ve tried manipulating the formula to show the single select option but I continue to get errors.
What I’m trying to accomplish is -
rather than the formula showing past due on those marked ‘complete’ it says “All Done” or just blank.

Okay, that’s an easy tweak. Just change the first few lines of my last iteration to this (replacing “Status” with the name of your single-select status field):

IF(
    Status="COMPLETE",
    "",
    IF(  
        IS_AFTER(
...

That’s how I changed it before I asked and I’m still getting an error message? Status is the name of my field. :slightly_smiling_face:

IF(Status= "COMPLETE",
    "",IF(
    IS_AFTER(
        DATETIME_FORMAT(
            SET_TIMEZONE(
                TODAY(),
                "America/Central"
            ), "M/DD/YYYY"
        ),
        {Start Date}
    ), "😫 Past Due 😫",
    IF(
        IS_SAME(
            DATETIME_FORMAT(
                SET_TIMEZONE(
                    TODAY(),
                    "America/Central"
                ), "M/DD/YYYY"
            ),
            {Start Date}
        ),
        "🚚 Scheduled TODAY 🚚",
        IF(
            DATETIME_DIFF(
                {Finish Date},
                DATETIME_FORMAT(
                    SET_TIMEZONE(
                        TODAY(),
                        "America/Central"
                    ), "M/DD/YYYY"
                ),
                'days'
            ) = 1,
            "⏳ Scheduled for TOMORROW ⏳",
            IF(
                DATETIME_DIFF(
                    {Start Date},
                    DATETIME_FORMAT(
                        SET_TIMEZONE(
                            TODAY(),
                            "America/Central"
                        ), "M/DD/YYYY"
                    ),
                    'days'
                ) <= 7,
                "👀 NEXT WEEK 👀"
            )
        )
    )
)

Hi – I’m new to Airtable and to Community Forums, but am hoping this is an appropriate place to ask for help with the grammar of formula writing that involves dates.

My formula is WORKDAY(FirstPerfor,-2). I would love for the resulting date to include the day of the week. I believe that formula is DATETIME_FORMAT(‘MM/DD/YY–ddd’). What’s the correct way to combine the two formulas?

AND while I’m imposing on you – I’d be glad for a pointer about how to use a text editor when drafting formulas.

Thanks so much!

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.

After a quick play, I found a few problems:

1. The order of the checks makes a difference. The order that I found to work consistently is Past Due first, then Today, then Tomorrow, and finally Next Week (i.e. chronological order)

2. In order for {Scheduled Date} to compare correctly against TODAY(), the latter had to be forced into the correct time zone, which involved turning every TODAY() in your formula into:

   DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "America/Chicago"), "M/DD/YYYY")
   

3. Your DATETIME_DIFF functions had the first two items reversed, leading to negative numbers, so the some comparisons didn’t return the results you expected.

The final modified function that I was able to test successfully for all states is this:

IF(
    IS_AFTER(
        DATETIME_FORMAT(
            SET_TIMEZONE(
                TODAY(),
                "America/Chicago"
            ), "M/DD/YYYY"
        ),
        {Scheduled Date}
    ), "😫 Past Due 😫",
    IF(
        IS_SAME(
            DATETIME_FORMAT(
                SET_TIMEZONE(
                    TODAY(),
                    "America/Chicago"
                ), "M/DD/YYYY"
            ),
            {Scheduled Date}
        ),
        "🚚 Scheduled TODAY 🚚",
        IF(
            DATETIME_DIFF(
                {Scheduled Date},
                DATETIME_FORMAT(
                    SET_TIMEZONE(
                        TODAY(),
                        "America/Chicago"
                    ), "M/DD/YYYY"
                ),
                'days'
            ) = 1,
            "⏳ Scheduled for TOMORROW ⏳",
            IF(
                DATETIME_DIFF(
                    {Scheduled Date},
                    DATETIME_FORMAT(
                        SET_TIMEZONE(
                            TODAY(),
                            "America/Chicago"
                        ), "M/DD/YYYY"
                    ),
                    'days'
                ) <= 7,
                "👀 NEXT WEEK 👀"
            )
        )
    )
)

You’ll need to adjust the timezone setting for your local area, of course.

If you want to condense things a bit, you could make a formula field that just does the TODAY time zone force using that one-liner I listed above (again adjusted for your local time zone), then reference that field wherever it needs to occur. I made such a field called {Today}, which let me shrink the main formula down to this:

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