Calculating difference in hours between two dates/times

[moogy20]

I apologise in advance for my computer illiteracy. I have a spreadsheet for a surgical study that I am doing and I would like to be able to calculate the time difference in hours please.

The details are as follows:

A2: Date of admission (eg 04/08/2010 in UK terms, ie dd/mm/yyyy)
B2: Time of admission (eg 22:47 -I used a 24 hour clock)
C2: Date of surgery (eg 05/08/2010)
D2: Time of surgery (eg 10:50)

I would like to be able to calculate the difference in hours between these dates/times. Some of the later intervals are more than 24 hours.

Many thanks for any help

 

[VoG]

Try

=24*((C2+D2)-(A2+B2))

format as Number.

 

[T. Valko]

I apologise in advance for my computer illiteracy. I have a spreadsheet for a surgical study that I am doing and I would like to be able to calculate the time difference in hours please.

The details are as follows:

A2: Date of admission (eg 04/08/2010 in UK terms, ie dd/mm/yyyy)
B2: Time of admission (eg 22:47 -I used a 24 hour clock)
C2: Date of surgery (eg 05/08/2010)
D2: Time of surgery (eg 10:50)

I would like to be able to calculate the difference in hours between these dates/times. Some of the later intervals are more than 24 hours.

Many thanks for any help

Try this...

=(C2+D2)-(A2+B2)

Format as [h]:mm

Result: 12:03

 

[MrVillareal]

Try this.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Time</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Admission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">22:47</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">40394.95</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Surgery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/5/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10:50</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">40395.45</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Difference in Hours</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">12.05</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet41</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=SUM(<font color="Blue">B2:C2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">=SUM(<font color="Blue">B3:C3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D4</th><td style="text-align:left">=(<font color="Blue">D3-D2</font>)*24</td></tr></tbody></table></td></tr></table><br />

 

[moogy20]

.

 

[moogy20]

Try this...

=(C2+D2)-(A2+B2)

Format as [h]:mm

Result: 12:03

Thanks for this. Sorry (I never use excel) but how do I format please?

 

[im2bz2p345]

Thanks for this. Sorry (I never use excel) but how do I format please?

Right click on the cell that you want to format, click "Format Cells," in the "Number" tab select "Custom" from the Category list on the left, then select the format "h:mm" Finally just press the "OK" button.

P.S. - You can also format different ways if you click on "Time" instead of "Custom" from the Category list on the left. Excel will preview how your cell with look as you click on different formats.

Hope that helps,

~ Im2bz2p345

 

[T. Valko]

Thanks for this. Sorry (I never use excel) but how do I format please?

Select the cell with the formula.
Right click
Select Format Cells
On the Number tab, from the Category drop down list select Custom
In the little box under Type, enter the format code: [h]:mm
OK out

 

[Haldoug]

Shortcut to Format (if you hand is not on the mouse to right click)
CTRL-1

 

[moogy20]

Select the cell with the formula.
Right click
Select Format Cells
On the Number tab, from the Category drop down list select Custom
In the little box under Type, enter the format code: [h]:mm
OK out

Fantastic. Thanks for all your help! One last thing. I have to do that calculation for a few hundred procedures. Do I have to enter a variant of the formula for each one or is there an easier was to do this?

Thanks