Dates and Formulas

#1

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.

0 Likes

#2

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!

0 Likes

#3

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.

0 Likes

#4

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.

1 Like

#5

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

0 Likes

#6

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

0 Likes

#7

Can you share the formula you are using?

0 Likes

#8

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

0 Likes

#9

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

0 Likes

#10

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') &lt;=7, "👀 NEXT WEEK 👀", IF(IS_AFTER(TODAY(), {Scheduled Date}), "😫 Past Due 😫"))))```
0 Likes

#11

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

#12

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)

0 Likes

#13

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

0 Likes

#14

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. :slight_smile:

0 Likes

#15

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

0 Likes

#16

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

#17

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

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

#18

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. :slight_smile:

2 Likes

#19

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

0 Likes