# Calculate hours worked minus a half hour for lunch

D

#### Danedel

I'm trying to find the formula to calculate the employee hours worked in a
given day allowing for a half hour lunch. The time card entry looks like:

Time in: 6:00
Time out: 14:30
I want the answer to be 8

R

#### Roger Govier

Hi

Excel stores times as fractions of a day, so 1 hour is 1/24, hence half hour
is 1/48
With Start time in A1 and End Time in B1
=B1-A1-1/48
For the cells with the formula, Format>Cells>Number>Custom>hh:mm

If you need to Sum the hours derived by the formula, format that cell as
[h]:mm to allow it to accumulate past 24 hours.

If your start and end times are likely to span over 2 days, then use
=MOD(B1-A1,1)-1/48

F

#### Fred Smith

And, if you want the formula to be clearer that it's 30 minutes, use:

=b1-a1-time(0,30,0)

Regards,
Fred.

Roger Govier said:
Hi

Excel stores times as fractions of a day, so 1 hour is 1/24, hence half
hour is 1/48
With Start time in A1 and End Time in B1
=B1-A1-1/48
For the cells with the formula, Format>Cells>Number>Custom>hh:mm

If you need to Sum the hours derived by the formula, format that cell as
[h]:mm to allow it to accumulate past 24 hours.

If your start and end times are likely to span over 2 days, then use
=MOD(B1-A1,1)-1/48

--
Regards
Roger Govier

Danedel said:
I'm trying to find the formula to calculate the employee hours worked in
a
given day allowing for a half hour lunch. The time card entry looks like:

Time in: 6:00
Time out: 14:30
I want the answer to be 8

P

#### Peo Sjoblom

Or just

=B1-A1-"00:30"

--

Regards,

Peo Sjoblom

Fred Smith said:
And, if you want the formula to be clearer that it's 30 minutes, use:

=b1-a1-time(0,30,0)

Regards,
Fred.

Roger Govier said:
Hi

Excel stores times as fractions of a day, so 1 hour is 1/24, hence half
hour is 1/48
With Start time in A1 and End Time in B1
=B1-A1-1/48
For the cells with the formula, Format>Cells>Number>Custom>hh:mm

If you need to Sum the hours derived by the formula, format that cell as
[h]:mm to allow it to accumulate past 24 hours.

If your start and end times are likely to span over 2 days, then use
=MOD(B1-A1,1)-1/48

--
Regards
Roger Govier

Danedel said:
I'm trying to find the formula to calculate the employee hours worked in
a
given day allowing for a half hour lunch. The time card entry looks
like:

Time in: 6:00
Time out: 14:30
I want the answer to be 8