Function macros are small programs that perform specialised calculations in Excel formulas. You do not need to know how the macro works, just copy it to the Excel file where you want to use it and then use it in a formula it. See below for detailed instructions.
Function SHIFTIME(Start_Time As Date, End_Time As Date)
'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
<strong>How to Copy and Use this Macro</strong>
Firstly, select all the text of the macro (including the line at the beginning starting with the word Function and the line at the end, End Function) and press CTRL+C to copy.
Then switch over to Excel and go to the Visual Basic Editor, ALT+F11. Press CTRL+R to open the Project Explorer, select your workbook file in the listing, right-click and choose Insert, Module.
Paste the macro into the module by pressing CTRL+V.
Finally, return to Excel (press ALT+F11 again) and select the cell where you want to enter your
formula. Choose Insert, Function then go to the User-Defined category and choose the SHIFTIME function from the list. Fill-in the Start_Time and End_Time arguments with the relevant cell references on your worksheet.
If you like the code and live in London try one of our Excel courses for everyone else download the free training manual EXCEL VBA