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
Best answer by augmented
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!
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?
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
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.
If I unselect (Use the same time zone (GMT) for all collaborators, the workday function produces a Sunday as well.
I think the issue is with the formatting tab in the formula field. Here’s what mine looks like.
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!!
That was the ticket augmented! I must have clicked that off accidentally. It works now!!
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?
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!
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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
