Time registration with Day and Night shift hours

Use VBA to calculate hours worked for different time shifts.

The whole process of time registration becomes more difficult when someone is able to start a work shift before midnight. You might then have to take into account the fact that you are dealing with two different days.

It could also be that a manager wants to see how many hours have been worked during a particular period. For instance because extra fees have to be paid when someone has worked during a night shift.

Assuming that such a night shift takes place from 00:00 to 06:00, you have to deal with five possible scenario’s to calculate the total hours worked.  

• Case 01 – Start before 00:00 and End before 06:00
• Case 02 – Start before 00:00 and End after 06:00
• Case 03 – Start between 00:00 and 06:00 and End before 06:00
• Case 04 – Start between 00:00 and 06:00 and End after 06:00
• Case 05 – Start after 06:00 and End after 06:00

So, if you have to calculate the hours worked when taking these conditions into account, what should you do? Should you work with functions and formulas, or should you write some VBA code?

I did not consider building a (mega) formula to solve this issue a wise thing to do (but if someone does know or is able to create such a formula, please let me know), so I wrote a VBA procedure. This article describes how to use VBA to calculate a total hours worked, assuming that one can start before midnight and work during different time shifts.

How does it work?

Time in Excel, is just a number. Actually, time is a number from 0 to 1, where 0 reflects 00:00:00 hours and where 1 reflects 24:00:00. Everything in between indicates a fraction of an hour. This means that a cell containing the value 0,5 (0.5 in the US) shows 12:00:00 hours when it is formatted as time (with format hh:mm:ss).

It also works the other way. When you enter 18:00:00 in a cell and format that cell as a number with two decimals, the contents of that cell becomes 0,75 (0.75 in the US).

Basically, this is the most important thing that you need to know in case you want to solve this issue with VBA.

If you want to know more about how Excel handles time, you can click here to learn from Microsoft.

To better understand the next steps, you might as well download the example workbook below. Because of security issues, the example workbook is of the .xlsx file type and does NOT contain any VBA procedure.

Does this mean that the workbook is useless?

No, it does not!

The code that you need to calculate the total hours worked is copied to the VBA CODE worksheet. You can attach this code to a code module, save the workbook as an .xlsm file and still do the job!

When you take a closer look at the VBA procedure from the example file, you can distinguish three sections of code.

The first section is about the declaration of variables which are used in the VBA procedure. The second section contains the references to the workbook and the worksheets. The third section is about looping through all the records and the calculation of the hours worked. In this section you find two variables.

• cRecDATA > The current record on worksheet Data.
• lRecDATA > The last record on worksheet Data.

The third section of code is what this example is all about.

Normally, time registration, or calculating the hours worked, involves no more than subtracting the Start time from the End time. In this case, that won’t always work. The possibility to start a shift before midnight and end a shift before or after 06:00, leads to a total of three calculations that may have to be made for each of the five situations mentioned above.

Figure 1, shows the workbook that I created. I contains a table in which the cells with a green font-color are used for input. All other cells are calculated automatically. The VBA procedure described in this article, focuses on columns H to M.

Technically, as you can see in figure 1, all five situations in this example deal with two cells and a possible ‘time-factor”. These cells are the Start time in column H and the End time in Column I. As VBA is working with the R1C1 notation, this means that you have to refer to column 8 and column 9 in your code module. The time-factor is a number, being the equivalent for 00:00 or 06:00.

Below are five blocks of VBA code that you can find on the VBA CODE worksheet from the example workbook. These blocks of code represent the five possible scenario’s of calculating the hours worked in this example.

How do they work?

‘Case 01 – Start before 00:00 and End before 06:00

If .Cells(cRecDATA, 8) > .Cells(cRecDATA, 9) And .Cells(cRecDATA, 9) <= 0.25 Then

‘Part 01 – Hours before 00:00

.Cells(cRecDATA, 11) = 1 – .Cells(cRecDATA, 8)

‘Part 02 – Hours between 00:00 and 06:00

.Cells(cRecDATA, 12) = .Cells(cRecDATA, 9)

End If

In normal language, this section of code reads:

If the Start-time is greater than the End-time and the End-time is less than or equal to 0.25 then

• The Day Total = 1 – Start-time
• The Night Total = End-time

This can best be explained with the first entry (row 10) displayed in figure 1.

In this entry, the Start-time in H10 is 22:00 and the End-time in I10 is 05:00. How do these entries fit into the two conditions?

• Condition 1: Is the Start-time greater than the End-time? The answer is Yes!  (22:00 is greater than 05:00)
• Condition 2: Is the End-time less than or equal to 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (05:00 is less than 06:00)

This means that both conditions are true and that Excel, or better, VBA, can execute the next lines of code.

• The Day Total = 1 – Start-time. As 1 formatted as Time in Excel, equals 24:00, the Total Hours worked during the Day shift is 02:00. (24:00-22:00)
• The Night Total = End-time. The End-time equals 05:00, so the total Hours worked during the Night shift is 05:00.

‘Case 02 – Start before 00:00 and End after 06:00

If .Cells(cRecDATA, 8) > .Cells(cRecDATA, 9) And .Cells(cRecDATA, 9) > 0.25 Then

‘Part 01 – Hours before 00:00

.Cells(cRecDATA, 11) = 1 – .Cells(cRecDATA, 8)

‘Part 02 – Hours between 00:00 and 06:00 (always 6)

.Cells(cRecDATA, 12) = 0.25

‘Part 03 – Hours after 06:00

.Cells(cRecDATA, 11) = .Cells(cRecDATA, 11) + (.Cells(cRecDATA, 9) – 0.25)

End If

In normal language, this section of code reads:

If the Start-time is greater than the End-time and the End-time is greater than 0.25 then

• The Day Total = 1 – Start-time
• The Night Total = 6
• The Day Total = The Day Total + (End-time – 0.25)

This can best be explained with the second entry (row 11) displayed in figure 1.

In this entry, the Start-time in H11 is 22:00 and the End-time in I11 is 08:00. How do these entries fit into the two conditions?

• Condition 1: Is the Start-time greater than the End-time? The answer is Yes!  (22:00 is greater than 08:00)
• Condition 2: Is the End-time greater than 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (08:00 is greater than 06:00)

This means that both conditions are true and that Excel, or better, VBA, can execute the next lines of code.

• The Day Total = 1 – Start-time. As 1 formatted as Time in Excel, equals 24:00, the Total Hours worked during the Day shift is 02:00. (24:00-22:00)
• The Night Total = 6. The End-time is greater than 0.25 so that person has worked the whole night shift, which is 06:00 hours.
• The Day Total = The Day Total + (End-time 1 – 0.25) As .25 formatted as Time in Excel, equals 06:00, the additional total hours worked during the Day shift is 02:00. (08:00-06:00). The grand total hours for the Day shift is therefore 04:00.

‘Case 03 – Start between 00:00 and 06:00 and End before 06:00

If .Cells(cRecDATA, 8) >= 0 And .Cells(cRecDATA, 8) < 0.25 And .Cells(cRecDATA, 9) =< 0.25 Then

‘Part 01 – Hours between 00:00 and 06:00

.Cells(cRecDATA, 12) = .Cells(cRecDATA, 9) – .Cells(cRecDATA, 8)

End If

In normal language, this section of code reads:

If the Start-time is greater than or equal to 0 and the Start-time is less than 0.25 and the End-time is less than or equal 0.25 then

• The Night Total = End-time – Start-time

This can best be explained with the third entry (row 12) displayed in figure 1.

In this entry, the Start-time in H12 is 01:00 and the End-time in I12 is 05:00. How do these entries fit into the two conditions?

• Condition 1: Is the Start-time greater than or equal to 0? The answer is Yes!  (01:00 is greater than 00:00)
• Condition 2: Is the Start-time less than 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (01:00 is less than 06:00)
• Condition 3: Is the End-time less than or equal to 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (05:00 is less than 06:00)

This means that all three conditions are true and that Excel, or better, VBA, can execute the next line of code.

• The Night Total = End-time – Start-time. The Total Hours worked during the Night shift is 04:00. (05:00-01:00)

‘Case 04 – Start between 00:00 and 06:00 and End after 06:00

If .Cells(cRecDATA, 8) >= 0 And .Cells(cRecDATA, 8) < 0.25 And .Cells(cRecDATA, 9) > 0.25 Then

‘Part 01 – Hours between 00:00 and 06:00

.Cells(cRecDATA, 12) = 0.25 – .Cells(cRecDATA, 8)

‘Part 02 – Hours after 06:00

.Cells(cRecDATA, 11) = .Cells(cRecDATA, 9) – 0.25

End If

In normal language, this section of code reads:

If the Start-time is greater than or equal to 0 and the Start-time is less than 0.25 and the End-time is greater than 0.25 then

• The Night Total = 0.25 – Start-time
• The Day Total = Start-time – 0.25

This can best be explained with the fourth entry (row 13) displayed in figure 1.

In this entry, the Start-time in H13 is 05:00 and the End-time in I13 is 14:00. How do these entries fit into the two conditions?

• Condition 1: Is the Start-time greater than or equal to 0? The answer is Yes!  (05:00 is greater than 00:00)
• Condition 2: Is the Start-time less than 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (05:00 is less than 06:00)
• Condition 3: Is the End-time greater than 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (14:00 is greater than 06:00)

This means that all three conditions are true and that Excel, or better, VBA, can execute the next lines of code.

• The Night Total = 0.25 – Start-time. As .25 formatted as Time in Excel, equals 06:00, the total hours during the Night shift is 01:00 (06:00 – 05:00)
• The Day Total = Start-time – 0.25. As .25 formatted as Time in Excel, equals 06:00, the total hours during the Day shift is 08:00 (14:00 – 06:00)

‘Case 05 – Start after 06:00 and End after 06:00

If .Cells(cRecDATA, 8) < .Cells(cRecDATA, 9) And .Cells(cRecDATA, 8) > 0.25 Then

‘Part 01 – Hours after 06:00

.Cells(cRecDATA, 11) = .Cells(cRecDATA, 9) – .Cells(cRecDATA, 8)

End If

In normal language, this section of code reads:

If the Start-time is less than the End-time and the End-time is greater than 0.25 then

• The Day Total = End-time – Start-time

This can best be explained with the fifth entry (row 14) displayed in figure 1.

In this entry, the Start-time in H14 is 08:00 and the End-time in I14 is 17:00. How do these entries fit into the two conditions?

• Condition 1: Is the Start-time less than the End-time? The answer is Yes!  (08:00 is less than 17:00)
• Condition 2: Is the End-time greater than 0.25? As 0.25 formatted as Time in Excel is 06:00, the answer is Yes! (17:00 is greater than 06:00)

This means that both conditions are true and that Excel, or better, VBA, can execute the next lines of code.

• The Day Total = Start-time – End-time. The total hours during the Day shift is 09:00 (17:00 – 08:00)

The final part of this VBA procedure is about calculating the totals. In fact it is just one line of code.

‘Add to Total

.Cells(cRecDATA, 13) = .Cells(cRecDATA, 11) + .Cells(cRecDATA, 12) – .Cells(cRecDATA, 10)

In normal language, this section of code reads:

• The Grand Total = Day Total + Night Total – Break

When you examine the first entry in row 10, the Grand Total = 02:00 + 05:00 – 00:30. The result is 06:30.

In this article I have described how you can use VBA to calculate a total number of hours worked, taking into account a number of conditions.

I hope that this piece of information about how you can use VBA to solve time registration issues is helpful. Remember that it is just an example and that there could very well be other ways to solve this.

Feel free to leave your comments or questions and please report about possible typo’s that I may have made!