Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formatting DateAdd formula result

Topic Labels: Formulas
Solved
Jump to Solution
945 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Comnenius
4 - Data Explorer
4 - Data Explorer

Hello

I've managed to create a formula that adds x number of days to a start date and pushes the result to a work day if the end date is Saturday or Sunday:

IF(AND({Direction start date},{Number of days},"days"),WORKDAY({Direction start date},{Number of days}),"days"),{Number of days})

The result seems to work fine, however the formating isn't great as the answer is is like this 2023-06-02T00:00:00.000Z .  I've clicked the format option but that doesn't let me do anything.  Any suggestions would be gratefully received!

 

1 Solution

Accepted Solutions
Stephen_Orr1
10 - Mercury
10 - Mercury

The formula has some errors in it. Here's what it looks like expanded for readability:

 

IF(
    AND(
        {Direction start date},
        {Number of days},
        "days"
        ),
        WORKDAY(
            {Direction start date},
            {Number of days}
        ),
    "days"
)
        ,{Number of days}
        )

 

This is saying all 3 of Direction start date, Number of days, or "days" = true, then do the workday calculation, otherwise output "days". There are some issues:
- Inside of AND(), "days" is unnecessary because it will always evaluate to true.
- Because the IF() can output either a date or text, Airtable cannot treat the formula field as a date nor provide you a way to format it as a date. The field is being treated as text with the date converted to text.

This last part is strange and probably unnecessary.

,{Number of days}
)

 Try this:

IF(
    AND({Direction start date}, {Number of days}),
    WORKDAY({Direction start date}, {Number of days})
)

-Stephen

See Solution in Thread

2 Replies 2
Stephen_Orr1
10 - Mercury
10 - Mercury

The formula has some errors in it. Here's what it looks like expanded for readability:

 

IF(
    AND(
        {Direction start date},
        {Number of days},
        "days"
        ),
        WORKDAY(
            {Direction start date},
            {Number of days}
        ),
    "days"
)
        ,{Number of days}
        )

 

This is saying all 3 of Direction start date, Number of days, or "days" = true, then do the workday calculation, otherwise output "days". There are some issues:
- Inside of AND(), "days" is unnecessary because it will always evaluate to true.
- Because the IF() can output either a date or text, Airtable cannot treat the formula field as a date nor provide you a way to format it as a date. The field is being treated as text with the date converted to text.

This last part is strange and probably unnecessary.

,{Number of days}
)

 Try this:

IF(
    AND({Direction start date}, {Number of days}),
    WORKDAY({Direction start date}, {Number of days})
)

-Stephen

Thanks for your help Stephen - it works fine!