Help

Re: Formula to calculate Due Date as a Workday

Solved
Jump to Solution
2431 6
cancel
Showing results for 
Search instead for 
Did you mean: 
K3ssen
6 - Interface Innovator
6 - Interface Innovator

I have a “Start Date” field and a “# Duration (days)” field and would like a formula that calculates the “Due Date” in work days.

I tried this formula from a YouTube tutorial:

IF(AND({Start Date},{Duration (days)}),WORKDAY({Start Date},{Duration (days)}))

But if I enter 2/15/21 (a Monday), and add a Duration of 10 days, the formula shows 2/28/21 (a Sunday) which is not a work day.

Is there something missing? Thanks, Kevin

1 Solution

Accepted Solutions

It CAN be done, good sir. It’s your AND() statement that is kicking your IF statement into the false state. Amend your formula as so…

IF(AND({Start Date},{Duration (days)}),WORKDAY({Start Date},{Duration (days)}), {Start Date})

Cheers!

See Solution in Thread

7 Replies 7
augmented
10 - Mercury
10 - Mercury

Hi Kevin. I replicated your example (using different, field names) and your formula works for me. I get 3/1/2021 for 10 days and 2/26/2021 for 9 days. Did you copy/paste that formula from the formula field in Airtable?

Thanks for your response augmented. Yes, I copied and pasted the formula from the formula field in Airtable, but am not getting a workday when using a Monday for the 10 day duration or a Friday for the 1 day duration. Both return a Sunday date as the result. Here’s a link to a screen capture that shows the formula and result.

https://play.vidyard.com/k8fvRyyeMpjbUX3L1iCPKE

Kevin

I think the issue is with the formatting tab in the formula field. Here’s what mine looks like.

image

If I unselect (Use the same time zone (GMT) for all collaborators, the workday function produces a Sunday as well.

That was the ticket augmented! I must have clicked that off accidentally. It works now!!

K3ssen
6 - Interface Innovator
6 - Interface Innovator

One last question…

When I put in “1” for duration, it shows the due date as the next day. If I put in “0” it won’t compute. What would you put for tasks that can be completed in a single day? So, the Start Date and Due Date would be the same date. Can that be done?

It CAN be done, good sir. It’s your AND() statement that is kicking your IF statement into the false state. Amend your formula as so…

IF(AND({Start Date},{Duration (days)}),WORKDAY({Start Date},{Duration (days)}), {Start Date})

Cheers!

That worked! Thanks again!! Have a good day!