Topic Labels: Formulas
Solved
447 2
cancel
Showing results for
Did you mean:
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
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

2 Replies 2
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

4 - Data Explorer

Thanks for your help Stephen - it works fine!