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