Finding Out Working Day from the Calendar Date using a formula

[aravindkm]

Hi All,

Can anyone please helpw me with a formula to find out 'Working Day' from the Calendar Date.

For Eg. My Weekend holidays were on Fridays and Saturdays so that i need to get my holiday marked on those days and also my work starts from Mid Month henec we used to update working days as negative numbers as well as positive numbers.

We can Mark fridays and Saturdays as "H" or Can Leave blank.

Example shown below:

Date	Day	Working Day
3/19/2017	Sun	-10
3/20/2017	Mon	-9
3/21/2017	Tue	-8
3/22/2017	Wed	-7
3/23/2017	Thu	-6
3/24/2017	Fri	H
3/25/2017	Sat	H
3/26/2017	Sun	-5
3/27/2017	Mon	-4
3/28/2017	Tue	-3
3/29/2017	Wed	-2
3/30/2017	Thu	-1
3/31/2017	Fri	H
4/1/2017	Sat	H
4/2/2017	Sun	1
4/3/2017	Mon	2
4/4/2017	Tue	3
4/5/2017	Wed	4
4/6/2017	Thu	5
4/7/2017	Fri	H
4/8/2017	Sat	H
4/9/2017	Sun	6
4/10/2017	Mon	7
4/11/2017	Tue	8
4/12/2017	Wed	9
4/13/2017	Thu	10
4/14/2017	Fri	H
4/15/2017	Sat	H
4/16/2017	Sun	11
4/17/2017	Mon	12
4/18/2017	Tue	13

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[Tetra201]

See if the following formula works for you -- it should be placed on row 2 and drag-copied down:

=IF(A2=WORKDAY.INTL(A2-1,1,"0000110"),NETWORKDAYS.INTL(EOMONTH(A$2,0)+1,A2,"0000110")+(WEEKDAY(EOMONTH(A$2,0)+1)<6)*(MONTH(A$2)=MONTH(A2)),"H")

 

[aravindkm]

Thanks a lot Tetra.

 

[aravindkm]

Hi tetra,

One query from the below formula.

=IF(A2=WORKDAY.INTL(A2-1,1,"0000110"),NETWORKDAYS.INTL(EOMONTH(A$2,0)+1,A2,"0000110")+(WEEKDAY(EOMONTH(A$2,0)+1)<6)*(MONTH(A$2)=MONTH(A2)),"H")

As per this formula it picks Friday and Saturday as holiday. If i want Saturday and Sunday to mark as holiday what should be the criteria to given like "0000110"

Thanks

 

[Tetra201]

... If i want Saturday and Sunday to mark as holiday what should be the criteria to given like "0000110"...

Here it is:

=IF(A2=WORKDAY.INTL(A2-1,1,"0000011"),NETWORKDAYS.INTL(EOMONTH(A$2,0)+1,A2,"0000011")+(WEEKDAY(EOMONTH(A$2,0)+1,2)<6)*(MONTH(A$2)=MONTH(A2)),"H")

 

[aravindkm]

Thanks Tetra.