Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

If statement with date formula

Topic Labels: Formulas
2418 2
cancel
Showing results for 
Search instead for 
Did you mean: 
S_Pacheco-Willi
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

S_Pacheco-Willi
5 - Automation Enthusiast
5 - Automation Enthusiast

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