Decimal Difference Between Two Dates and Times

[highflight1985]

Hello. I've looked around but can't find a scenario exactly like mine or one similar that answered my question. So, here goes:

I have two dates and times, where the dates are in their own cells, and the times are in their own as well. So, four cells. I need to calculate the decimal number of hours (hh.mmmmmm) between those dates and times so that I can use the result in another equation.

So, my worksheet looks like this:

A B C D
1
2
3 Start Date Start Time End Date End Time
4 1/23/11 14:34 1/24/11 17:19
5
6
7


I had it all nice and spaced, but forum trimmed extra spaces....sorry.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Start Date</td><td style=";">Start Time</td><td style=";">End Date</td><td style=";">End Time</td><td style="text-align: right;;"></td><td style=";">Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/23/2011</td><td style="text-align: right;;">14:34</td><td style="text-align: right;;">1/24/2011</td><td style="text-align: right;;">17:19</td><td style="text-align: right;;"></td><td style="text-align: right;;">26.75</td></tr></tbody></table><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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">C4+D4</font>)-(<font color="Red">A4+B4</font>)</font>)*24</td></tr></tbody></table></td></tr></table><br />

Note: Excel may try to automatically format the cell with the formula as a date. You may have to change the format to number or general.

 

[highflight1985]

Thanks. That solves part 1 of my problem. Now, part 2.

Given a start date and time of 1/1/10 at 11:00, and an end time of 1/1/10 at 17:34, your formula calculates 6.57 hours. (Which is correct of course.)

If the driver drove 300 miles in that 6.57 hours, his MPH should be 45.66. If the value of 300 were in cell K4 and the result of my equation is in Y4, then this should work:

Y4 = (300/F4) (where F4 is 6.57, the cell shown in your example).

However, this is what I'm getting as a result instead: <TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=70><COLGROUP><COL style="WIDTH: 53pt" width=70><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=18 width=70 align=right>1096.26</TD></TR></TBODY></TABLE>

Any ideas?

 

[AlphaFrog]

It worked for me. Double check your values or references in the formulas.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>K</th><th>Y</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Start Date</td><td style=";">Start Time</td><td style=";">End Date</td><td style=";">End Time</td><td style="text-align: right;;"></td><td style=";">Hrs</td><td style=";">Miles</td><td style=";">MPH</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">17:34</td><td style="text-align: right;;"></td><td style="text-align: right;;">6.57</td><td style="text-align: right;;">300</td><td style="text-align: right;;">45.68528</td></tr></tbody></table><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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">C4+D4</font>)-(<font color="Red">A4+B4</font>)</font>)*24</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Y4</th><td style="text-align:left">=K4/F4</td></tr></tbody></table></td></tr></table><br />

 

[highflight1985]

Disregard...I messed up on some ('s and )'s and so it wasn't calculating correctly.