Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate hours worked minus a half hour for lunch | Excel Worksheet Functions | |||
how to calculate hours worked daily & minus 30 min lunch, 7am-5pm | Excel Discussion (Misc queries) | |||
How can I calculate daily hours worked minus lunch to eual a decim | Excel Worksheet Functions | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
Calculate hours worked | New Users to Excel |