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:

[TABLE="width: 221"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Working Day[/TD]
[/TR]
[TR]
[TD="align: center"]3/19/2017[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]-10[/TD]
[/TR]
[TR]
[TD="align: center"]3/20/2017[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]-9[/TD]
[/TR]
[TR]
[TD="align: center"]3/21/2017[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]-8[/TD]
[/TR]
[TR]
[TD="align: center"]3/22/2017[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]-7[/TD]
[/TR]
[TR]
[TD="align: center"]3/23/2017[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]-6[/TD]
[/TR]
[TR]
[TD="align: center"]3/24/2017[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]3/25/2017[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]3/26/2017[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]-5[/TD]
[/TR]
[TR]
[TD="align: center"]3/27/2017[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]-4[/TD]
[/TR]
[TR]
[TD="align: center"]3/28/2017[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]-3[/TD]
[/TR]
[TR]
[TD="align: center"]3/29/2017[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]-2[/TD]
[/TR]
[TR]
[TD="align: center"]3/30/2017[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]-1[/TD]
[/TR]
[TR]
[TD="align: center"]3/31/2017[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/1/2017[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/2/2017[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4/3/2017[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4/4/2017[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4/5/2017[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4/6/2017[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]4/7/2017[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/8/2017[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/9/2017[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]4/10/2017[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4/11/2017[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]4/12/2017[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]4/13/2017[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]4/14/2017[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/15/2017[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4/16/2017[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]4/17/2017[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]4/18/2017[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]13[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[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.