Adding up working hours

[Newbienew]

I am using template out of Excel that i am modifying. The issue I am having is I don't know how to add how many hours are between AM and PM so if its 8a to 4p I need to know how many hours are in between but I would need it to be displayed 8.0
How i have it laid is
[TABLE="width: 288"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]AM
[/TD]
[TD]PM[/TD]
[TD]Week 1
[/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD] 8:00 am
[/TD]
[TD] 4:00 pm
[/TD]
[TD="align: right"]8.0
[/TD]
[TD] 1.0
[/TD]
[/TR]
</tbody>[/TABLE]


I have several cells i have to format with the same formula if someone could please provide assistance it would be awesome Thank You

 

[63falcondude]

You have a few choices here. If you want the value itself to be 8, you can use this:

=(B2-A2)*24

If you want it to only be displayed as 8 but actually be the percentage of the day (33%), you can use this:

=B2-A2 custom formatted as h

 

[Newbienew]

Will that keep the AM and PM, because this is a 24 hour shift. so how would that work out

 

[63falcondude]

I'm not sure what you mean. You example shows that it is an 8 hour shift, not a 24 hour shift.

If the shift can be in multiple days, I would include the days in the times to do the calculations.

You can always format the cells to show the times only.

 

[Newbienew]

Ok so let say you work from 8am to 4pm on Monday which is 8 hours. Then on Tuesday you work 2pm to 8pm which is 6 hours. And on Thursday you work 10pm to 6am which is 8 hours.
On my first posting I made one column AM and another PM. But now it IN and OUT where AM and PM are. So I need to display whether its AM or PM in either cell and still be able to calculate the hours in between. I hope this provided better clarity then i did before. Sorry and thank you

 

[Newbienew]

<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: rgb(38, 38, 38); font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { color: rgb(64, 64, 64); font-size: 9pt; font-family: "Century Gothic",sans-serif; }.xl66 { }.xl67 { border: 0.5pt solid windowtext; }.xl68 { color: rgb(10, 35, 56); font-family: "Century Gothic",sans-serif; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: rgb(56, 122, 171) currentcolor rgb(56, 122, 171) rgb(56, 122, 171); background: rgb(223, 236, 244) none repeat scroll 0% 0%; }.xl69 { color: rgb(10, 35, 56); font-family: "Century Gothic",sans-serif; border: 0.5pt solid windowtext; background: rgb(223, 236, 244) none repeat scroll 0% 0%; }.xl70 { color: rgb(64, 64, 64); font-size: 9pt; font-family: "Century Gothic",sans-serif; border: 0.5pt solid windowtext; }.xl71 { font-family: "Arial Unicode MS"; }</style> [TABLE="width: 334"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:5857;width:117pt" width="156"> <col style="mso-width-source:userset;mso-width-alt:2816;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2740;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2635; width:53pt" width="70" span="2"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 156"]January[/TD]
[TD="class: xl69, width: 75"]In[/TD]
[TD="class: xl69, width: 73"]Out[/TD]
[TD="class: xl69, width: 70"]Week 1[/TD]
[TD="class: xl69, width: 70"]Overtime[/TD]
[/TR]
[TR]
[TD="class: xl65"]Monday[/TD]
[TD="class: xl70, align: right"]8:00 AM[/TD]
[TD="class: xl70, align: right"]1:00 PM[/TD]
[TD="class: xl71, align: right"]5.0[/TD]
[TD="class: xl67, align: right"]3.0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Tuesday[/TD]
[TD="class: xl70, align: right"]9:00 PM[/TD]
[TD="class: xl70, align: right"]3:00 AM[/TD]
[TD="class: xl71, align: right"]-18.0[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Wednesday[/TD]
[TD="class: xl70, align: right"]10:00 PM[/TD]
[TD="class: xl70, align: right"]2:00 AM[/TD]
[TD="class: xl71, align: right"]-20.0[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Thursday[/TD]
[TD="class: xl70, align: right"]8:00 AM[/TD]
[TD="class: xl70, align: right"]9:00 AM[/TD]
[TD="class: xl71, align: right"]1.0[/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

So I have part of it figured out but where it says 9:00 PM to 3:00 AM is the issue now. Your input of the formula worked out but I am looking to see if it can work in reverse as well so I do not get the negative that you see displayed. Thank you and thank you in advance.

 

[Newbienew]

Any assistance please

 

[mrshl9898]

In D2

=IF((C2-B2)*24<0,24-((B2-C2))*24,(C2-B2)*24)

 

[Newbienew]

@ Mrshl9898-Thank you SOOOOOOOO much. You are awesome