Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

If statement with date formula

Topic Labels: Formulas
766 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there! I have the following categories in my table: Task Due Date, Weeks From Launch (Formula Field) and Launch Date.

I want to write a formula in the Weeks From Launch category where if the Task Due Date is blank, then record cell stays blank, otherwise it uses the Launch Date and Task Due Date fields to calculate weeks from launch.

My current formula (returning an error message):
IF({TASK DUE DATE} = “”, “”, DATETIME_DIFF({TASK DUE DATE}, {LAUNCH DATE (from PROJECT)}, ‘WEEKS’)

Any suggestions? Thank you!

2 Replies 2

Hey @S_Pacheco-Williams!

Here’s a revised version of your posted formula:

IF(
    {Task Due Date},
    ABS(
        DATETIME_DIFF(
            {Task Due Date},
            {Launch Date},
            'weeks'
        )
    ) & " Weeks From Launch"
)

That formula will produce this:

image

You’ll just need to switch in your exact field names.


I do, however, have some bonus content for you…

I was quite bothered by the #ERROR that returns should the Launch Date field remain blank.
So I spiced it up with this version of the formula:

IF(
    AND(
        {Task Due Date},
        {Launch Date}
    ),
    ABS(
        DATETIME_DIFF(
            {Task Due Date},
            {Launch Date},
            'weeks'
        )
    ) & " Weeks From Launch",
    IF(
        AND(
            {Task Due Date},
            {Launch Date} = 0
        ),
        "Missing Launch Date!",
        IF(
            AND(
                {Task Due Date} = 0,
                {Launch Date}
            ),
            "Missing Due Date!"
        )
    )
)

That formula will produce this:

image


Let me know if you have any questions or want anything tweaked.
I’m bored.

Thank you! Could we adjust it so the dates show as negative (-15 weeks from launch)?