If statement using dates

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:

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(
    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!"
        )
    )
)

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!