networking days based on sunday

[zia.butt]

I want formula of networking days if only sunday is considered off only. means one off in a week.

 

[zia.butt]

any body knows about the networking days formula based on only one off in a week.

 

[Marcelo Branco]

Hi,

What Excel version are you using?

M.

 

[zia.butt]

excel 2007.

 

[Shweta]

Hi,

You can do it by VBA.

Shweta

 

[T. Valko]

excel 2007.

Do you need to account for holidays?

 

[zia.butt]

yes, need for account holiday. or mgiht be used for any other purpose

 

[T. Valko]

yes, need for account holiday. or mgiht be used for any other purpose

Try this...

A2 = start date
B2 = end date
C2:C10 = list of holiday dates to exclude from the count

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<7),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

 

[Shweta]

Below is the VBA Code:

Function six_days_working(start_date As Long, End_date As Long) As Long
Dim i, j As Long
For i = start_date To End_date
If VBA.Weekday(i, vbMonday) < 7 Then
j = j + 1
End If
Next
six_days_working = j
End Function

Hope this will help.....

Shweta

 

[Shweta]

Hi T.Valko,

I appreciate your formula. It works fine.

But I need a favour from you can you please breakup this formula because I am unable to understand the logic behind this.

Regards
Shweta