You track a team’s shifts in a sheet and the night shift from 22:00 to 06:00 comes out negative. On top of that, when you add up the week, the total seems to reset.
How to use Hours Worked Formula step by step
1. Choose the calculation
Hours between two times, as a decimal, minus lunch, overtime, pay, adding hours to a time, a column total or conversion between h:mm and decimal.
2. Give the cells
Type the clock-in cell, the clock-out cell and the extra data such as lunch minutes or the hourly rate.
3. Apply the right format
Copy the formula and give the cell time format, or [h]:mm if the total passes 24 hours. The decimal uses number format.
The Step Count: Traditional Friction vs. KorexTools
Working out a night shift’s hours by hand (4 steps)
- Subtract clock-in from clock-out.
- See a negative result or #####.
- Add 24 hours by hand in the night case.
- Fix the affected cells one by one.
With the KorexTools “Hours Worked Formula” builder (3 steps)
- You pick the time calculation.
- You give clock-in, clock-out and extra data.
- You copy the formula and apply the format indicated.
Excel stores times as fractions of a day, and that is where nearly every error comes from
A time for Excel is a number between 0 and 1: 12:00 is 0.5. Subtracting clock-out minus clock-in works until the shift crosses midnight and the result turns negative. The MOD function with divisor 1 fixes it, because it returns only the fractional part. To get decimal hours, multiply by 24.
To add up a column that passes 24 hours the SUM formula is right, but the result looks like it resets if the cell has the normal h:mm format. You need the [h]:mm format. To calculate pay you always convert to decimal before multiplying by the rate, because 7:30 multiplied directly gives a meaningless value.
What to do in each case
- •Shift crossing midnight: MOD(out - in, 1).
- •Pay by the hour: convert to decimal and multiply.
- •Total above 24 h: SUM with [h]:mm format.
Clock-in 22:00 in A2 and clock-out 06:00 in B2
| What you calculate | Formula | Result |
|---|---|---|
| Hours worked | =MOD(B2-A2,1) | 8:00 with time format |
| As a decimal | =MOD(B2-A2,1)*24 | 8 |
| Overtime beyond 6 hours | =MAX(0,MOD(B2-A2,1)*24-6) | 2 |
Common Mistakes and How to Avoid Them
- ⚠️Forgetting the [h]:mm format and believing the sum is wrong.
- ⚠️Multiplying the rate by h:mm without converting to decimal.
Frequently Asked Questions
Why do I get ##### when subtracting times?
Because the result is negative, usually from a shift crossing midnight. Use the MOD variant this tool generates and it goes away.
Why is my total lower than expected?
Because the h:mm format resets every 24 hours. Change the cell format to [h]:mm to see the real total.
How do I turn 7:30 into 7.5?
Multiply the cell by 24 and give it number format, or use the convert-to-decimal option that separates hours and minutes.
What I would do if I were in your shoes
«Good time tracking starts with cells formatted as time, not as text.» — DataKorex Engineering Team.