Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 01, 2023 11:10 AM
I have used a formula to create a new date value based on another date cell:
The Next Due Date uses the following formula:
Solved! Go to Solution.
Feb 01, 2023 11:39 AM
Hey @RoseAmelia!
The reason that your Next Due Date field is returning a string value instead of a date value is because the DATETIME_FORMAT() function returns a formatted string.
Since you're simply trying to format the date value into a "DD/MM/YYYY" format, we can actually format the date value without having to utilize the DATETIME_FORMAT() function.
To do this, plug in the following formula:
IF(
AND(
{Frequency}, {Task Due}
),
SWITCH(
{Frequency},
"Weekly", DATEADD(
{Task Due}, 1, "week"
),
"Monthly", DATEADD(
{Task Due}, 1, "month"
),
"Quarterly", DATEADD(
{Task Due}, 3, "month"
)
)
)
Since the formula knows that we're expecting a date value, you'll be able to hop into the Formatting tab in the formula field's configuration.
You'll want to be sure that the time and time zone settings are configured how you'd like them.
Here's the final behavior:
Feb 01, 2023 11:39 AM
Hey @RoseAmelia!
The reason that your Next Due Date field is returning a string value instead of a date value is because the DATETIME_FORMAT() function returns a formatted string.
Since you're simply trying to format the date value into a "DD/MM/YYYY" format, we can actually format the date value without having to utilize the DATETIME_FORMAT() function.
To do this, plug in the following formula:
IF(
AND(
{Frequency}, {Task Due}
),
SWITCH(
{Frequency},
"Weekly", DATEADD(
{Task Due}, 1, "week"
),
"Monthly", DATEADD(
{Task Due}, 1, "month"
),
"Quarterly", DATEADD(
{Task Due}, 3, "month"
)
)
)
Since the formula knows that we're expecting a date value, you'll be able to hop into the Formatting tab in the formula field's configuration.
You'll want to be sure that the time and time zone settings are configured how you'd like them.
Here's the final behavior:
Feb 03, 2023 12:51 PM
Thank you so much for your help!
I've been using AirTable for years and just starting to explore the scripting and formula aspects. I appreciate this community forum very much!!