Microsoft Excel - Working out the difference in Hours

The DATEDIF function in Excel can be extremely useful. But the time intervals it handles are only day, month and year.

There's another function available in Excel DATEDIFF that can help with hours.

Creating a Custom function

Function DiffInHours(First, Second)

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

End Function

Entering a 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