Help

Formatting DateAdd formula result

Topic Labels: Formulas
Solved
Jump to Solution
522 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!