How do I format a cell to calculate overnight Hours?

[Erica]

I am putting together a spreadsheet that calculates the hours worked in
military time basesd on the clock in/out time, however the formula I am using
will not calculate the hours worked for an overnight associate. I.e. clocks
in @ 9:00 PM and clocks out at 7:00 AM
The formula I am using is as follows:

=IF(((C9-B9)*24)>=5,C9-(B9+TIME(0,30,0)),C9-B9)*24

Thank you!!

 

[ShaneDevenshire]

hI,

Try something like this

=IF(C9<B9,1-B9+C9,IF((C9-B9)*24>=5,C9-B9+TIME(0,30,0),C9-B9)*24)

 

[Bernard Liengme]

=C9-B9+(B9>C9) and format cell with [h]:mm ... the [] allows for more than
12 hours
OR
=(C9-B9+(B9>C9))*24 and format cell Number 2-decimals

best wishes

 

[RagDyeR]

Try this:

=IF(MOD(C9-B9,1)*24>=5,MOD(C9-B9,1)*24-0.5,MOD(C9-B9,1)*24)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I am putting together a spreadsheet that calculates the hours worked in
military time basesd on the clock in/out time, however the formula I am
using
will not calculate the hours worked for an overnight associate. I.e. clocks
in @ 9:00 PM and clocks out at 7:00 AM
The formula I am using is as follows:

=IF(((C9-B9)*24)>=5,C9-(B9+TIME(0,30,0)),C9-B9)*24

Thank you!!

 

[Erica]

Bernard, thank you for your help! This formula =(C9-B9+(B9>C9))*24 does
calculate the over night hours, however, I also need the formula to
automatically deduct 1/2 hour break... Can you help me with the formula that
would calculate this as well?

Thank you!!!

=C9-B9+(B9>C9) and format cell with [h]:mm ... the [] allows for more than
12 hours
OR
=(C9-B9+(B9>C9))*24 and format cell Number 2-decimals

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am putting together a spreadsheet that calculates the hours worked in
military time basesd on the clock in/out time, however the formula I am
using
will not calculate the hours worked for an overnight associate. I.e.
clocks
in @ 9:00 PM and clocks out at 7:00 AM
The formula I am using is as follows:

=IF(((C9-B9)*24)>=5,C9-(B9+TIME(0,30,0)),C9-B9)*24

Thank you!!

 

[Bernard Liengme]

How many 1/2hr breaks and when?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard, thank you for your help! This formula =(C9-B9+(B9>C9))*24 does
calculate the over night hours, however, I also need the formula to
automatically deduct 1/2 hour break... Can you help me with the formula
that
would calculate this as well?

Thank you!!!

=C9-B9+(B9>C9) and format cell with [h]:mm ... the [] allows for more
than
12 hours
OR
=(C9-B9+(B9>C9))*24 and format cell Number 2-decimals

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am putting together a spreadsheet that calculates the hours worked in
military time basesd on the clock in/out time, however the formula I am
using
will not calculate the hours worked for an overnight associate. I.e.
clocks
in @ 9:00 PM and clocks out at 7:00 AM
The formula I am using is as follows:

=IF(((C9-B9)*24)>=5,C9-(B9+TIME(0,30,0)),C9-B9)*24

Thank you!!

 

[Erica]

This formula worked!!
Because of you, I will look good at work... (I'll make sure to give credit
where credit is due) Thank you!

 

[RagDyeR]

You're welcome, and appreciate the feed-back.

BTW, the credit *is* due you!

You were able to find a solution.

That's really all that counts.