Quick Time Calculation

karmaimages

Board Regular
Joined
Oct 1, 2009
Messages
65
I have a list of cells which are formatted as such:

COL A COL B
01/01/2001 08:00 05/01/2001 11:59

Which is the easy way to tell the difference between in days/hours between the time.

Many thanks
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Code:
=b1-a1
and format the cell as time, perhaps a custom format such as
dd "days" hh "hours"
 

karmaimages

Board Regular
Joined
Oct 1, 2009
Messages
65
I tried:

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" span=3 width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=108 align=right x:num="40137.375">20/11/2009 09:00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=108 align=right x:num="40138.458333333336">21/11/2009 11:00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 width=108 align=right x:num="1.0833333333357587" x:fmla="=B1-A1"></TD></TR></TBODY></TABLE>

had those set to dd-mm-yyyy hh:mm

and set the result cell to time and it comes out at 02:00:00

which is wrong as its 1 day and 2 hours?
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
You need to apply the right format to the cell containing the formula. Try the format I suggested.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,173
Members
414,509
Latest member
Cdavis7078

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top