Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Utilize Date conditions after creating date token in formula

Solved
Jump to Solution
1789 2
cancel
Showing results for 
Search instead for 
Did you mean: 
RoseAmelia
5 - Automation Enthusiast
5 - Automation Enthusiast

I have used a formula to create a new date value based on another date cell: Screen Shot 2023-02-01 at 12.05.39 PM.png

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.
Screen Shot 2023-02-01 at 12.05.56 PM.png
 
Is there a way to filter Next Due Date to be 'within' and 'the past week'?
 
Thank you!
 
1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

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.

Ben_Young1_0-1675280256189.png

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:

Ben_Young1_1-1675280313271.png

 

See Solution in Thread

2 Replies 2
Ben_Young1
11 - Venus
11 - Venus

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.

Ben_Young1_0-1675280256189.png

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:

Ben_Young1_1-1675280313271.png

 

RoseAmelia
5 - Automation Enthusiast
5 - Automation Enthusiast

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