Skip to main content

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!

 

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

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!

Reply


Terms & Conditions