Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 2nd 09, 04:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 1
Default I need to calculate hours worked from 11pm to 7am minus a lunch

Hours worked is 11pm to 7am. Need a formula to calculate total hours worked
minus the 30 minute break if worked over 6 hours over the midnight hours.

Used IF((C41-B41)*246,(C41-B41)*24-0.5,(C41-B41)*24) were B41=7am and
C41=3pm. This works then but not with the 11pm to 7am timeframe.
--
Paula

  #2   Report Post  
Old June 2nd 09, 04:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default I need to calculate hours worked from 11pm to 7am minus a lunch

You may wish to try replacing each instance of (C41-B41) with MOD(C41-B41,1)
--
David Biddulph

"Paula Brooks" wrote in message
...
Hours worked is 11pm to 7am. Need a formula to calculate total hours
worked
minus the 30 minute break if worked over 6 hours over the midnight hours.

Used IF((C41-B41)*246,(C41-B41)*24-0.5,(C41-B41)*24) were B41=7am and
C41=3pm. This works then but not with the 11pm to 7am timeframe.
--
Paula



  #3   Report Post  
Old June 2nd 09, 05:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default I need to calculate hours worked from 11pm to 7am minus a lunch

Hi,

Need some clarity he

If the total time is <= 24 hours then

=MOD(B2-A2,1)

calculates the time. to handle the other part you need to tell use what the
"midnight hours" are. Also, are you saying that if they work 22 hours but it
doesn't cross midnight (1 AM to 11 PM) they get no break? Also, suppose they
work 6 hrs & 15 minutes do you count this as 6 hours or as 5 hrs & 45 minutes?

Toe give a 30 minute break if 6 hours are worked:

=MOD(B2-A2,1)-IF(MOD(B2-A2,1)=6/24,0.5/24)

The results need to be formatted to time to show as hours. Or the final
results need to be multiplied by 24.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Paula Brooks" wrote:

Hours worked is 11pm to 7am. Need a formula to calculate total hours worked
minus the 30 minute break if worked over 6 hours over the midnight hours.

Used IF((C41-B41)*246,(C41-B41)*24-0.5,(C41-B41)*24) were B41=7am and
C41=3pm. This works then but not with the 11pm to 7am timeframe.
--
Paula



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
Calculate hours worked minus a half hour for lunch Danedel Excel Worksheet Functions 3 August 29th 08 01:12 AM
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
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis Graham Excel Discussion (Misc queries) 2 January 28th 07 08:40 PM
Calculate hours worked larry_saudi New Users to Excel 5 October 11th 06 07:46 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017