I need to calculate hours worked from 11pm to 7am minus a lunch

P

Paula Brooks

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)*24>6,(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.
 
D

David Biddulph

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

Shane Devenshire

Hi,

Need some clarity here:

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top