Forum Discussion
Displaying Time in hours
I have a spread sheet where I record my work hours. Eg: 0700 (A2) - 1530 (A3) which equals 8 1/2 hours, then I take 30 minutes off for a lunch break, which leaves me with 8 hours. My formula is A3-A...
Your setup (times entered as numbers, e.g. 0700, 1530)
A (Start) | B (End) | C (Break in minutes) | D (Worked Hours) | E (Hourly Rate) | F (Daily Pay) |
0700 | 1530 | 30 | 8.00 | 20.00 | 160.00 |
Formatting
- Columns A & B: custom number format 0000 (so 700 shows as 0700)
- Column C: number, 0 (no decimals)
- Column D: number, 0.00
- Columns E & F: currency, e.g. € #,##0.00
The formula (place in D2 and fill down)
=ROUND(((INT(B2/100)+MOD(B2,100)/60)-(INT(A2/100)+MOD(A2,100)/60)+IF(B2<A2,24,0))-(C2/60),2)
How it works
- INT(…/100) gives the hour (e.g. 15 from 1530)
- MOD(…,100)/60 converts minutes to fractional hours (e.g. 30/60 = 0.5)
- IF(B2<A2,24,0) adds 24 hours for shifts crossing midnight (e.g. 2200–0600)
Subtracts the break in minutes divided by 60 (so you simply type 30)
- ROUND(…,2) prevents hidden floating‑point errors in pay roll
Pay formula (F2)
=ROUND(D2*E2,2)
Weekly totals (optional)
- Total hours: =ROUND(SUM(D2:D8),2)
- Total pay: =ROUND(SUM(F2:F8),2)
Why this is the right solution for you
- You keep entering 0700 and 1530 exactly as you do now — no colons, no time‑format confusion.
- The break is entered as plain minutes (30), which feels natural.
- Day shifts and night shifts both work perfectly.
- The result is a clean decimal hour (8.00) that you can multiply directly by your hourly rate — no extra conversion steps.
- The ROUND wrapper ensures you never see a tiny rounding error in your payslip.
If you ever change your break to 45 minutes or 1 hour, just type 45 or 60 in column C — the formula handles the rest.
My answers are voluntary and without guarantee!
Hope this will help you.