OzGrid Excel and VBA Newsletter March 2008

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

LEARN EXCEL AND/OR VBA RIGHT NOW FOR ONLY $29.95 (HALF PRICE)

WANT DEDICATED 1-ON-1 HELP? ENTIRE PROJECT DONE FOR YOU AT A DISCOUNT?

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 toDaddylonglegs a formula and Excel guru who frequents theOzgrid 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 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