Displaying Time in hours

Lynda53 wrote:

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

With hours in text format, you can try this:

=REPLACE(A3,3,0,":")-REPLACE(A2,3,0,":")-"00:30"

adding the colon to text, Excel cast the string in time values and calculate the difference. 

The final string "00:30" which indicates your break, you can replace it with a reference to a cell containing the half hour or any other custom intervals.

Bye.

Your setup (times entered as numbers, e.g. 0700, 1530)

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.

Lynda53​ 

Enter the times (start and end) as 7:00 and 15:30. Excel should automatically recognize these as time values and format the accordingly. Then your formula can be =A3-A2-30/1440 as shown in the picture.