Help

Re: Utilize Date conditions after creating date token in formula

Solved
Jump to Solution
1578 0
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

 

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