Help

If statement using dates

Topic Labels: Dates & Timezones
1627 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Chattanooga_Fun
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a “deadline date” and and we have either a 3 months or 6 months, our two different project time lines we use.

So when I set the field to 3 months it will take the project start date, add 3 months and the if the project goes over 90 days, to pop up into my past due view.

I want when the deadline date has been exceeded based upon the current actual date for it to jump over to my past due view.

For example, I had a project start on Dec.24th and because today is March 24th, if the project is not completed today, tomorrow that project would show up on my past due view report.
Thank you in advance!

2 Replies 2

Hey @Chattanooga_Funding!

I had a lot of fun with this one.

For the sandbox I built this in, I used the following field names:

  1. Summary (This is the formula field)
  2. Start Date (Date/Time)
  3. Timeline (Single Select)

Here’s the final product:

image

Here’s the actual formula:

IF(
    AND(
        {Start Date},
        {Timeline}
    ),
    "Goal Date: " & 
    IF(
        {Timeline} = "Three Month Window",
        DATETIME_FORMAT(
            DATEADD(
                {Start Date},
                3,
                'months'
            ),
            'l'
        ),
        IF(
            {Timeline} = "Six Month Window",
            DATETIME_FORMAT(
                DATEADD(
                    {Start Date},
                    6,
                    'months'
                ),
                'l'
            )
        )
    )
    & "\n" &
    "Till Deadline: " &
    DATETIME_DIFF(
        IF(
            {Timeline} = "Three Month Window",
            DATETIME_FORMAT(
                DATEADD(
                    {Start Date},
                    3,
                    'months'
                ),
            'l'
            ),
            IF(
                {Timeline} = "Six Month Window",
                DATETIME_FORMAT(
                    DATEADD(
                        {Start Date},
                        6,
                        'months'
                    ),
                    'l'
                )
            )
        ),
        TODAY(),
        'days'
    )
    & " days"
)
&
IF(
    AND(
        {Start Date},
        {Timeline}
    ),
    IF(
        IS_SAME(
            {Start Date},
            TODAY()
        ),
        "\n" & "🌟 Due Today",
        IF(
            IS_BEFORE(
                DATEADD(
                    {Start Date},
                    IF(
                        {Timeline} = "Six Month Window",
                        '6',
                        '3'
                    ),
                    'months'
                ),
                TODAY()
            ),
            "\n" & "⚠ Overdue!"
        )
    )
)

How does it work?

The formula only returns something if both the timeline and the start date are provided.
Hence the blank records in my screenshot.

It will then use your selection of either six or three months as you define in the single-select field.
With this, it will then add the respective number of months to the start date and return the date value.

Next, it will calculate the time remaining in days.
Finally… if the due date is today, it will return the “ :star2: Due Today” string, and if it’s after today, it will return the “ :warning: Overdue!” value.


If you intend on copying and pasting that formula into your base, please be sure to change out the field names for your own.
If you have trouble with it, be sure to also check to see if you have some sort of way of declaring a six or three-month project window.

I recommend the single-select field.
If you want to have a project status condition that prevents the overdue flag based on whether or not the project is completed, we can easily toss that into the formula as well.
You can let me know if you want that in, and I can toss you the edited formula.

Let us know if you have any issues or additional questions.

If there was a emote that is beyond mind blown, this would be the picture of just that of these ( ) …
Thank you beyond measure. I have an avg ability using airtable but use it for everything and this forum and community are so amazing!!! Thank you so much!
I can’t wait to set this up and start saving time not having to dig through so many records to find this data, and eliminate missing something. thank you!