Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 28th 08, 03:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
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  
Old August 28th 08, 03:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
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


  #3   Report Post  
Old August 28th 08, 11:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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" <[email protected] 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



  #4   Report Post  
Old August 29th 08, 01:12 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
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" <[email protected] 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 03:29 PM


All times are GMT +1. The time now is 06:31 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017