Utilize Date conditions after creating date token in formula

Question

I have used a formula to create a new date value based on another date cell:

The Next Due Date uses the following formula:

SWITCH(Frequency,

'Weekly', DATETIME_FORMAT(

DATEADD({Task Due}, 1, 'week'), 'DD/MM/YYYY'),

'Monthly', DATETIME_FORMAT(

DATEADD({Task Due}, 1, 'month'), 'DD/MM/YYYY'),

'Quarterly', DATETIME_FORMAT(DATEADD({Task Due}, 3, 'month'), 'DD/MM/YYYY'))

However, to set up views and automations I need to be able to use the conditional properties of a date value, and I can not seem to filter on any of the normal date values.

Is there a way to filter Next Due Date to be 'within' and 'the past week'?

Thank you!

Ben_Young1 · Accepted Answer

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:

RoseAmelia · Answer

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: 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!!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded