If statement with date formula

[S_Pacheco-Willi]

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!

[Ben_Young1]

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:

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:

---

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

[S_Pacheco-Willi]

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