Calculate Date Difference excluding Weekend (Saturday & Sunday)

[oscorp]

Hi,

I am just wondering, if anyone can help me with the formula for calculating the difference between 2 dates excluding saturday and sunday?

I do not want to use NETWORKDAYS function (as users dont want to use any add-ins)

Please let me know...!

regs
oscorp

[JimmyTheHand]

Hello Oscorp, welcome to board! :hi:

Take a look at this workbook. The result is marked blue.
The function is complicated and long, so I broke it down to parts (see cells C1, C2, D1, D2). You can assemble it into one cell, if you feel like.

HTH
Jimmy

[Zack Barresse]

Hi there,

There is a list of functions native to Excel which can be substituted if one does not have the ATP...

http://www.dailydoseofexcel.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

For your specific question, you can use a shorter one than listed on that link though..

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+A2-A1)/7))

.. where A1 is the start date and A2 is the end date.

Take care!

[JimmyTheHand]

Zack, I've got two questions.

1) What's that part in the formula between {}. What is it about, how do you use it.

2) Why doesn't work your formula for me.

Perhaps an answer to Q1 makes me able to answer Q2.


Jimmy

[Zack Barresse]

Hi Jimmy,

The squiggly brackets ( { and } ) enclose arrays in worksheet formulae. Not really sure why it doesn't work for you. Maybe the value in A2 is not later than the value in A1??

[oscorp]

Thanks a lot Zack....it worked.
In the meanwhile I wrote following function...but it is not working...can anyone tell me why??


Function CalcWorkDays(Data1 As Range, Data2 As Range)
Dim Strt As Double, Endd As Double
Dim Dys As Long, i As Long
Strt = Data1(1)
Endd = Data2(1)
Dys = 0
For i = Int(Strt) To Int(Endd)
If Weekday(i) <> 1 And Weekday(i) <> 7 Then
Dys = Dys + 1
End If
Next
CalcWorkDays = Dys
End Function



regs
Oscorp

[JimmyTheHand]

Try this way:
Dim Strt As Long, Endd As Long

[Bob Phillips]

oscorp,

It works fine for me. What do you get?

BTW, it can be slightly simplified to



Function CalcWorkDays(Data1 As Range, Data2 As Range)
Dim Strt As Double, Endd As Double
Dim Dys As Long, i As Long
Strt = Data1(1)
Endd = Data2(1)
Dys = 0
For i = Int(Strt) To Int(Endd)
If Weekday(i, vbMonday) < 6 Then
Dys = Dys + 1
End If
Next
CalcWorkDays = Dys
End Function


ANd what about holidays?

[Zack Barresse]

I would abandon i altogether. You won't match a native formulas efficiency.

[Shazam]

This might work also. But I like firefytr formula better because he is using the WEEKDAY function that I think will always recognize the 1900 & 1904 date system. The one below is just using mathmatical functions. More details to the link below.


=1+A2-A1-SUM(INT((A2-{2,7})/7)-INT((A1-{2,7}-1)/7))


http://support.microsoft.com/kb/180162