Adding workdays to a date (that is not counting Saturday and Sunday)

[stef]

Hi,
I'm trying to add a certain amount of working days to a date in a formula. There are at least 10 of these calculations in the formula. I'll post it below.
The working days are in bold.
C2 is the cell with receipt date and F2 is the cell with the rest of dates(this keeps changing based on the status of file). This formula is applied to an entire column btw.

=IF(Q2="Ack. Letter due 10 working days from stamped received date",C2+10,IF(Q2="Notification of decision due 30 working days from dispatch of ack. Letter",F2+30,IF(Q2="Notification of decision due30 working days from stamped received date of latest FI response",F2+30,IF(Q2="Notification of decision due in 30 working days",F2+30,IF(Q2="Notification of decision due in 30 working days",F2+30,IF(Q2="Notification of draft TOR due in 21 working days",F2+21,IF(Q2="Issue Final TOR within 10 days",F2+10,IF(Q2="Notification of decision due in 80 working days",F2+80,IF(Q2="Awaiting FI","waiting on applicant",IF(Q2="Awaiting agreement /modification request","waiting on applicant",IF(Q2="Awaiting EIA submission","waiting on applicant","")))))))))))

 

[Luke M]

Ouch, that looks long. Let's let the formula find the number for us, and simplify things. Also, to calculate the work, we'll use the WORKDAY function.

Here's the array formula:
=IFERROR(WORKDAY(F2, MID(Q2,MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,Q2),"")),2)),"waiting on applicant")

Confirm using Ctrl+Shift+Enter, not just Enter. Then copy down as needed.