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>

 

[mole999]

Do you mean

=text(date cell,"ddd")

 

[Tetra201]

The following formula works for the posted example:

=IF(A2=WORKDAY.INTL(A2-1,1,"0000110"),NETWORKDAYS.INTL($A$15,A2,"0000110"),"H")

 

[aravindkm]

Thanks for the reponse.

This formula is to finding out a day. I want to find out working day like if 1st April falls on Saturday then that will not be a working day since its a holiday. This is Just about finding out the working Days excluding holidays.

 

[aravindkm]

Thanks a lot Tetra201

This was perfectly working.

Thanks a ton

 

[Tetra201]

You are welcome.

 

[aravindkm]

Hi Tetra,

Is it possible to conditional format the holidays by greying out with colors using a VBA code.

 

[Tetra201]

Is it possible to conditional format the holidays by greying out with colors using a VBA code.

It is possible without VBA. For the posted example, apply conditional formatting to range C2:C32 using the following rule:

=$C2="H"

 

[aravindkm]

Hi Tetra

Whats happening here is im Keying the dates in the input sheet and deriving Days and working days using the formulas.

Then i have a very small macro where im pasting in the output sheet using a macro. I just wanted to use the macro and add in conditional macro with that macro.

My Macro is below to copy paste the date.

Sub copypaste()

Sheets("Input Sheet").Select

Range("B2:D48").Copy

Sheets("Ouput Sheet").Select

Range("E3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True

Range("A1").Select

End Sub

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

My Output Sheet

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

<tbody>
</tbody><colgroup><col><col span="33"></colgroup>

In this i want fridays and saturdays to be conditional formatted with gery colour.

Sorry for the inconvinence.

<tbody>
</tbody>

 

[aravindkm]

Hi Tetra,

Thanks for the formula

However while using the formula i fopund out that the last day of the month is derived as working day "1" instead of "-1".

Could you please let me know the posiible way to derive it as "-1" and First day of the next month as "1".

Thanks