Additional parameters using the WORKDAY.INTL function

[tobysdhc]

Hello,
I am using the following formula to determine a due date (you have 60 days to complete the assignments including weekends and excluding holidays)

=WORKDAY.INTL(A1,60,"0000000",C1:C9)
A1 = start date
60 = number of day to complete the assignment
“0000000” = every day is included (no weekends)
C1:C9 = list of holidays

Using the parameters above I get the date “dddd, mm/dd/yyyy”

Here is my dilemma, the due day cannot be a Saturday or a Sunday. If the due date falls on a Saturday or Sunday I need to automatically go back to the previous Friday. For example, is the due date lands on “Saturday, 10/24/2015” or “Sunday 10/25/2015” I need the due date to display as “Friday, 10/23/2015”

Your thoughts?

 

[DRSteele]

Maybe I'm wrong, but I think the function NETWORKDAYS.INTL does that very thing.

 

[DRSteele]

I misunderstood. I think this will do it:

=IF(WEEKDAY(WORKDAY.INTL(A1,60,"0000000",C1:C9),2)=6,WORKDAY.INTL(A1,60,"0000000",C1:C9)-1,IF(WEEKDAY(WORKDAY.INTL(A1,60,"0000000",C1:C9),2)=7,WORKDAY.INTL(A1,60,"0000000",C1:C9)-2,WORKDAY.INTL(A1,60,"0000000",C1:C9)))

 

[tobysdhc]

That is perfect, thank you!

So I am really going to test you now.

Absences may change the due date, but only for greater than 8 absences.

For example, if a person misses 8 days the due day will remain the same, but if they miss 9 or more, the absences minus 8, will be added to the due date.

For example, if a supervisor’s original due date was 11/1/2015 (using the formula above) all the way up to 8 absences it will remain 11/1/2015, but as soon as she reaches 9 absences the new due date is 11/2/2015 so on and so forth for each absence after that.

Here how I have my sheet set up
C D
4 start date 9/1/2015
5 absences XX
6 original due date Friday, 11/1/2015
7 adjusted due date dddd, mm/dd/yyyy

“Original Due Date” (D6) contains your formula (minor changes) =(IF(WEEKDAY(WORKDAY.INTL(D4,60,"0000000",C14:C99),2)=6,WORKDAY.INTL(D4,60,"0000000",C14:C99)-1,IF(WEEKDAY(WORKDAY.INTL(D4,60,"0000000",C14:C99),2)=7,WORKDAY.INTL(D4,60,"0000000",C14:C99)-2,WORKDAY.INTL(D4,60,"0000000",C14:C99))))


The absences will go in D5 - up to 8 no changes 9 or more the due date is adjust one day for each absence over 8 with that same weekend rule as above.

I tried adding another If function but kept returning an error message.

Any help would be appreciated

 

[DRSteele]

Huh? The more truant a supervisor the longer he has to complete the job? What kind of messed up company is this? If it's a public co I'll short the shares!

I'll attack your second problem when I'm in front of a pc. I laugh......

 

[tobysdhc]

Tell me about it. They have an excellent union. They all also get pulled off site for numerous activities so they are not truly absences. I have my suspicions though

 

[DRSteele]

I don't believe what I'm reading.

 

[DRSteele]

Ok, try this. I appended the formula to handle another IF statement. The amendment is lower case and has carriage returns, which won't matter to excel. I hope I did it right on this touchscreen.

=IF(WEEKDAY(WORKDAY.INTL(A1,60,"0000000",C1:C9),2)+if (d5 <=8,0,d5-8)=6,
if (d5 <=8,0,d5-8)+
WORKDAY.INTL(A1,60,"0000000",C1:C9)-1,

IF(WEEKDAY(WORKDAY.INTL(A1,60,"0000000",C1:C9),2) +if (d5 <=8,0,d5-8)=7,
if (d5 <=8,0,d5-8)+
WORKDAY.INTL(A1,60,"0000000",C1:C9)-2,

if (d5 <=8,0,d5-8)+
WORKDAY.INTL(A1,60,"0000000",C1:C9)))

 

[tobysdhc]

You're awesome! thank you for the help!

 

[tobysdhc]

I have encountered a problem.

When I enter the start date as "10/15/2015" it returns "Friday January 01, 2016" even though it is in the list of Holidays. This also happens for start date of "09/23/2015" which return "Friday November 27, 2015." Is seems like a may need a formula for it to exclude Friday's that are holidays

Which are

11/27/2015
12/25/2015
1/1/2016
2/5/2016
3/18/2016
3/25/2016

Your thoughts?