Back to Excel Newsletter Archives
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;
HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT),
"0 days " & ROUND(24*(EndDT-StartDT),2)&"hours",
" days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
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 toDaddylonglegs a formula and Excel guru who frequents theOzgrid forums.
His better way is;
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.
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 RangeDim 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 rangeSet 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 WithEnd 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 RangeDim lArea As LongDim 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 0If rRange Is Nothing Then lReply = MsgBox("Invalid Range.", vbRetryCancel) If lReply = vbRetry Then Run "SortNoncontiguousRanges" Else Exit Sub End IfEnd 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 WithEnd Sub
Got any Questions? Free Excel Help
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help