Tuesday 6 December 2016

Excel - Working out the difference in Hours

We saw how useful the DATEDIF function can be in an earlier article. But it's time intervals are only day, month and year there's another function available in Excel DATEDIFF but unlike the other one it's not actually supported as a worksheet function. It's a function in Excel's macro language, VBA. But that doesn't mean that we can't use it in our worksheet formulas. Read on... 

Creating a Custom function


Function DiffInHours(First, Second)


    DiffInHours = DateDiff("h", First, Second)


End Function

Entering a Custom Function


Entering an Excel custom function
















Settings
The interval argument has these settings:
Setting
Description
yyyy
Year
q
Quarter
m
Month
y
Day of year
d
Day
w
Weekday
ww
Week
h
Hour
n
Minute
s
Second


Shift Time function
Excel time values only function within the 24 hour day; if you start work at 19:00 hours and end at 23:00 hours then Excel will calculate the elapsed time as 4 hours. Should you work beyond midnight, then the same calculation will produce a negative value as you would have stopped working at a time that was a lesser value than your starting time. The following function accepts any starting or ending time and calculates the actual elapsed time in hours.

Public Function SHIFTIME(Start_Time As Date, End_Time As Date) As Variant
'Accepts : Working shift start and end times.
'Returns : Number of hours worked as a decimal value.
If End_Time >= Start_Time Then
'Day Shift.
SHIFTIME = DateDiff("n", Start_Time, End_Time)
Else
'Night Shift; time to midnight plus time from midnight.
SHIFTIME = _
DateDiff("n", Start_Time, 1) + DateDiff("n", 0, End_Time)
End If
SHIFTIME = SHIFTIME / 60
End Function

No comments:

Post a Comment