Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Formula:
=(Time out Time in) - 0.5 
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:
Formula:
=(B2-B1)-0.5 
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to calculate hours worked daily & minus 30 min lunch, 7am-5pm Debby_Jo Excel Discussion (Misc queries) 1 July 23rd 08 03:40 AM
How can I calculate daily hours worked minus lunch to eual a decim lili Excel Worksheet Functions 6 March 28th 07 03:28 AM
how do I round hours worked to the next half hour with a 24hr shif gycoso3 Excel Worksheet Functions 1 March 28th 06 09:25 PM
Calculate time difference to the half hour Ken Ivins Excel Worksheet Functions 6 July 17th 05 05:48 PM
How do I multiply rate per hour by hours worked. MAC Excel Worksheet Functions 1 December 22nd 04 04:29 PM


All times are GMT +1. The time now is 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"