Calculate work hours between 2 dates including saturday (in Excel 2007)?

[judhajit]

Hi, Please help I am trying to calculate work hours between 2 dates including weekends (in Excel 2007)?

Details are:
H2 - Raised_Date (Includes date and time)
I2 - End_Date (Includes date and time)
N2 - Start Time (10:30:00 AM)
O2 - End Time (10:00:00 PM)

In the formula be I am getting correct data for weekdays, but I am not able to insert Saturday as part of working days.

Formula used:
=IF(OR($O$2<$N$2,I2<H2),0,(NETWORKDAYS(H2,I2,1)-(NETWORKDAYS(H2,H2,1)*IF(MOD(H2,1)>$O$2,1,(MAX($N$2,MOD(H2,1))-$N$2)/($O$2-$N$2)))-(NETWORKDAYS(I2,I2,1)*IF(MOD(I2,1)<$N$2,1,($O$2-MIN($O$2,MOD(I2,1)))/($O$2-$N$2))))*($O$2-$N$2)*24)

 

[Declamatory]

Hi Judhajit

Instead of NETWORKDAYS use the NETWORKDAYS.INTL function. This allows you to stipulate what days are working days and which aren't.

NETWORKDAY.INTL(I2,I2,11,1) (11 is to say that Sundays only are weekend days)

 

[Declamatory]

Sorry,

[FONT=&quot]Excel 2010 introduced a new function NETWORKDAYS.INTL that lets you specify which days are "weekend" days, but this is not available in Excel 2007.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The following function has been adapted from http://blog.sangupta.com/2008/02/excel-networkdays-custom-function.html to count Saturdays as working days:[/FONT]
[FONT=&quot] [/FONT]

[COLOR=#333333][FONT=&quot]Function MyNetWorkDays(ByVal StartDate As Date, ByVal EndDate As Date, _
        Optional ByVal Holidays As Range = Nothing) As Long
    Dim diff As Long
    Dim weeks As Long
    Dim ed As Long
    Dim sd As Long
    Dim delta As Long
    Dim swap As Boolean
    Dim temp As Date
    Dim holiday As Variant
    Dim wh As Long
    swap = False
    If EndDate < StartDate Then
        'swap the dates
        temp = EndDate
        EndDate = StartDate
        StartDate = temp
        swap = True
    End If
    diff = EndDate - StartDate
    ed = Weekday(EndDate)
    sd = Weekday(StartDate)
    weeks = diff \ 7
    If ed = sd Then
        If Not (ed = 1) Then
            delta = 1
        End If
    ElseIf ed > sd Then
        If sd = 1 Then sd = 2
        delta = ed - sd + 1
    Else
        delta = 7 - (sd - ed)
    End If
    MyNetWorkDays = (weeks * 6) + delta
    ' check for holidays
    If Not Holidays Is Nothing Then
        For Each holiday In Holidays
            wh = Weekday(holiday)
            If Not (wh = 1) Then
                If StartDate <= holiday And holiday <= EndDate Then
                    MyNetWorkDays = MyNetWorkDays - 1
                End If
            End If
        Next holiday
    End If
    If swap Then
        MyNetWorkDays = 0 - MyNetWorkDays
    End If
End Function[/FONT][/COLOR]

 

[judhajit]

Thanks Declamatory, but I am using Excel 2007. In 2007 NETWORKDAYS.INTL does not work.

 

[Declamatory]

The following will calculate Saturdays as holidays

A1= Start date
B1= End data
C1:C8= Any holiday dates you want

=SUMPRODUCT(--(COUNTIF(C1:C8,ROW(INDIRECT(A1&":"&B1)))=0),--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),3)<6))