Holiday Tracker

[SamC799]

Hi, I am trying to prepare a holiday tracker for my team. I have a table with Statuary holidays dates, and run a formula for network days. However our employer has decided to reduce Fridays hours so Friday now becomes a 1/2 day holiday. Is there anyway I can alter my formula to make a Friday be half a day.

This is the formula I am using =NETWORKDAYS(B5,C5,$G$9:$G$15)
B5 = Start Date of Holiday
C5= End Date of Holiday
G9-G15 is the table of dates for statuary holidays

TIA

 

[Toadstool]

Hi SamC799,

So we want to take your elapsed days excluding weekends (assuming they are Saturday and Sunday) but treat Fridays as half a day.

If I use NETWORKDAYS.INTL I can use a bit pattern to specify which are non-working days starting with Monday so "0000111" specifies Friday, Saturday and Sunday as non-working days. If I then subtract your calculation from that new number I'll get a negative number of how many Fridays are between those dates (which are not also Holiday dates). So if I multiply that by 0.5 for the half day Friday then I can add that to your calculation to reduce the elapsed by the half days.

SamC799.xlsx
	A	B	C	D	E	F	G
1		Start	End	Old Result	New Result
2		05-Feb-21	08-Feb-21	2	1.5
3		02-Feb-21	05-Feb-21	4	3.5
4		02-Feb-21	04-Feb-21	3	3
5		02-Feb-21	08-Feb-21	5	4.5
6		05-Feb-21	12-Feb-21	6	5
7		01-Jan-21	31-Dec-21	259	233
8		01-Jul-21	07-Jul-21	4	3.5		Holidays
9		01-Jan-21	03-Jan-21	0	0		01-Jan-21
10		01-Jan-21	04-Jan-21	1	1		05-Jul-21
11		01-Jan-21	07-Jan-21	4	4
12		01-Jan-21	08-Jan-21	5	4.5
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=NETWORKDAYS(B2,C2,$G$9:$G$15)
E2:E12	E2	=NETWORKDAYS(B2,C2,$G$9:$G$15)+((NETWORKDAYS.INTL(B2,C2,"0000111",$G$9:$G$15)-NETWORKDAYS(B2,C2,$G$9:$G$15))*0.5)

 

[SamC799]

Thank you so much. That has worked perfectly

 

[SamC799]

Thank you so much. That has worked perfectly

Hi SamC799,

So we want to take your elapsed days excluding weekends (assuming they are Saturday and Sunday) but treat Fridays as half a day.

If I use NETWORKDAYS.INTL I can use a bit pattern to specify which are non-working days starting with Monday so "0000111" specifies Friday, Saturday and Sunday as non-working days. If I then subtract your calculation from that new number I'll get a negative number of how many Fridays are between those dates (which are not also Holiday dates). So if I multiply that by 0.5 for the half day Friday then I can add that to your calculation to reduce the elapsed by the half days.

SamC799.xlsx
	A	B	C	D	E	F	G
1		Start	End	Old Result	New Result
2		05-Feb-21	08-Feb-21	2	1.5
3		02-Feb-21	05-Feb-21	4	3.5
4		02-Feb-21	04-Feb-21	3	3
5		02-Feb-21	08-Feb-21	5	4.5
6		05-Feb-21	12-Feb-21	6	5
7		01-Jan-21	31-Dec-21	259	233
8		01-Jul-21	07-Jul-21	4	3.5		Holidays
9		01-Jan-21	03-Jan-21	0	0		01-Jan-21
10		01-Jan-21	04-Jan-21	1	1		05-Jul-21
11		01-Jan-21	07-Jan-21	4	4
12		01-Jan-21	08-Jan-21	5	4.5
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=NETWORKDAYS(B2,C2,$G$9:$G$15)
E2:E12	E2	=NETWORKDAYS(B2,C2,$G$9:$G$15)+((NETWORKDAYS.INTL(B2,C2,"0000111",$G$9:$G$15)-NETWORKDAYS(B2,C2,$G$9:$G$15))*0.5)

Thank you so much. That has worked perfectly

 

[Toadstool]

Thank you so much. That has worked perfectly

Thank you so much. That has worked perfectly

You're welcome!
Thanks for the feedback.

 

[Tetra201]

Here is how Toadstool's formula can be shortened:

=SUM(NETWORKDAYS.INTL(B2,C2,{"0000011","0000111"},$G$9:$G$15))/2