Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter March 2008

Search Tips FREE Download!

Got any Questions? Free Excel Help

EXCEL TIPS AND TRICKS

Return Total Work Hours Between 2 Dates & Times

This Month I would like to show you how to calculate the totals working hours between 2 dates & times. Or, to be exact, this is NOT my work but the work of others who are very proficient  with Excel. However, if you are NOT familiar with dates & times in Excel (most aren't even when they
believe they are).

This question arose in the Ozgrid forum earlier this week and rather than do the hard Yards, I Googled the Thread (new feature of the forum
and came up with this page from Chip Pearson the 1st formula on that page is;

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),
HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT),
"0 days " & ROUND(24*(EndDT-StartDT),2)&"hours",
MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart)))&
" days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))&" hours "))

Details are found on the URL above.

Before you all close the newsletter in horror, there is a better and simpler solution. While the above function certainly works, it has 2 potential pit-falls.

1) "DayStart" and "DayEnd" must be within normal business hours.

2) The result returned is text.

I'm a BIG believer in NEVER returning text when working with numbers, dates and/or times. Why? Because it makes down-stream calculations a LOT more difficult. IF that is not going to be an issue, the single best way to use the formula above is to use Relative Named Ranges  that match those in the Monster formula.

Ok, onto the 'better way'. All credit to Daddylonglegs a formula and Excel guru who frequents the Ozgrid forums.

His better way is;

=(NETWORKDAYS(StartDT,EndDT,HolidayList)-1)*(DayEnd-DayStart)+MOD(EndDT,1)-MOD(StartDT,1)

and then Custom Format the result cell to show total hours. That is: [h]:mm

Download a working sample of this formula.

BTW. The NETWORKDAYS Function requires the Analysis Toolpak to be installed. Tools>Add-ins.
 

EXCEL VBA TIPS & TRICKS

How To Easily Sort Multiple Ranges

The code below allows us to sort a non-contiguous range that is hard-coded and assumes no headers.

Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I

'Set range variable to non-contiguous range
Set rRange = Range("B1:C10,E1:F10,H1:I10")

    With rRange
        'Each contiguous range represents an Area
        For lArea = 1 To .Areas.Count
            With .Areas(lArea)
               .Sort Key1:=.Cells(1, 1), _
                Order1:=xlAscending, Header:=xlNo, _
                Orientation:=xlTopToBottom
                'XlSortOrientation can be one of these XlSortOrientation constants.
                'xlSortRows default. Sorts by row.
                'xlSortColumns. Sorts by column.
            End With
        Next lArea
    End With
End Sub

This one allows us to specify exactly which ranges to sort via the users selection (default) or via the InputBox . It assumes headers.

Sub SortNoncontiguousRanges2()
Dim rRange As Range
Dim lArea As Long
Dim lReply As Long


    'Set range variable to non-contiguous range
    On Error Resume Next
    Set rRange = Application.InputBox _
        (Prompt:="Select ranges while holding " _
        & "down Ctrl key. Include Headers", _
        Title:="Sort Non-Contiguous Range", _
        Default:=Selection.Address, Type:=8)
    On Error GoTo 0

If rRange Is Nothing Then
    lReply = MsgBox("Invalid Range.", vbRetryCancel)
        If lReply = vbRetry Then
           Run "SortNoncontiguousRanges"
        Else
           Exit Sub
        End If
End If

    With rRange
        'Each contiguous range represents an Area
        For lArea = 1 To .Areas.Count
            With .Areas(lArea)
               .Sort Key1:=.Cells(2, 1), _
                Order1:=xlAscending, Header:=xlYes, _
                Orientation:=xlTopToBottom
                'XlSortOrientation can be one of these _
                 XlSortOrientation constants.
                'xlSortRows default. Sorts by row.
                'xlSortColumns. Sorts by column.
            End With
        Next lArea
    End With
End Sub

Got any Questions? Free Excel Help

Instant Download and Money Back Guarantee on Most Software

Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

FREE Download!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help