Dates and Formulas

Holli_Younger · ‎Apr 09, 2019

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.

Justin_Barrett · ‎Apr 10, 2019

After a quick play, I found a few problems:

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

Holli_Younger · ‎Apr 11, 2019

Thank you Justin. This helped greatly. I was even able to add a few more formulas based on future dates (past the one week). I also tried a suggestion I found in the community from (Need a "Completed date" column: is there a "last updated" or similar formula?) on how to mark those appointments that are complete as Complete and not past due.
I’ve added a checkbox, but can’t figure out the formula here. I’ve tried adding a (timestamp) table like the example shows, but still not doing what I need. Can you suggest the completed formula if there is a a check box. (do I need to remove the date- which I will need for future references)

Holli_Younger · ‎Apr 11, 2019

Still trying to figure this one out - I found this on the airtable tutorial, but I’m assuming its being over read by the original formula for “past due”. How can I have both?

IF(Checkbox = 1, “A”, “B”) via: https://support.airtable.com/hc/en-us/articles/217113757-Using-checkbox-fields-in-formulas

Justin_Barrett · ‎Apr 14, 2019

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:

Holli_Younger · ‎Apr 15, 2019

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.

Justin_Barrett · ‎Apr 15, 2019

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

Holli_Younger · ‎Apr 15, 2019

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 👀"
            )
        )
    )
)

Justin_Barrett · ‎Apr 15, 2019

You’re missing a closing parenthesis at the end. After the Next Week output string, you have four closing parentheses. You should have five.

Stuff like that is tricky to track, which is one of the reasons I follow the example of some other folks here and use a separate text editor when constructing more complex formulas. :slightly_smiling_face:

Holli_Younger · ‎Apr 15, 2019

Yes!!! Thank you. You have been a huge help, not only just giving me the formula but providing instructions and explanations as well.

Amy_Asch · ‎Jul 17, 2019

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!