Remember Me?

#1
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
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
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
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
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
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
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
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Debby_Jo Excel Discussion (Misc queries) 1 July 23rd 08 03:40 AM lili Excel Worksheet Functions 6 March 28th 07 03:28 AM gycoso3 Excel Worksheet Functions 1 March 28th 06 09:25 PM Ken Ivins Excel Worksheet Functions 6 July 17th 05 05:48 PM MAC Excel Worksheet Functions 1 December 22nd 04 04:29 PM

All times are GMT +1. The time now is 09:32 PM.