Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 07, 2022 10:15 PM
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!
Mar 08, 2022 09:51 AM
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.
Mar 10, 2022 12:25 PM
Thank you! Could we adjust it so the dates show as negative (-15 weeks from launch)?