Calculate hours worked minus a half hour for lunch
 

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

Answer: Calculate hours worked minus a half hour for lunch
 

To calculate the hours worked minus a half hour for lunch, you can use the following formula in Microsoft Excel:

Here's how you can use this formula to calculate the hours worked in your example:

1. In a blank cell, type the equal sign (=) to start the formula.
2. Click on the cell containing the time out value (14:30 in this case).
3. Type a minus sign (-) to subtract the time in value.
4. Click on the cell containing the time in value (6:00 in this case).
5. Type another minus sign (-) to subtract the half hour lunch break.
6. Type 0.5 to subtract the half hour lunch break.
7. Press Enter to calculate the result.

The formula should look like this: Assuming that the time in value is in cell B1 and the time out value is in cell B2, the result should be 8.00, which represents the number of hours worked minus the half hour lunch break.

Calculate hours worked minus a half hour for lunch
 

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, FormatCellsNumberCustomhh: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" wrote in message
...
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

Calculate hours worked minus a half hour for lunch
 

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" <roger@technology4unospamdotcodotuk wrote in message
...
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, FormatCellsNumberCustomhh: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" wrote in message
...
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

Calculate hours worked minus a half hour for lunch
 

Or just


=B1-A1-"00:30"

--


Regards,


Peo Sjoblom

"Fred Smith" wrote in message
...
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" <roger@technology4unospamdotcodotuk wrote in message
...
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, FormatCellsNumberCustomhh: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" wrote in message
...
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