How to calculate time intervals between two moments in duration of several days

[Elle_saf]

Hello.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need to calculate the time intervals between two moments in duration of four days. The results in format of [h]:mm then will be used to draw a chart. <o></o>
<o></o>
I am using IF statement as =IF(A1>B1,B1+1-A1,B1-A1); however for times after 24h (next day) this statement does not work.
<o></o>
e.g., <o></o>
<o></o>
A1: 2010-0-01 11:15:00 00:00:00<o></o>
A2: 2010-0-01 14:15:00 03:00:00<o></o>
A3: 2010-0-01 15:47:00 04:32:00<o></o>
A4: 2010-0-01 18:02:00 06:47:00<o></o>
A5: 2010-0-02 12:25:00 01:10:00 must be 25:10:00<o></o>
A6: 2010-0-02 15:48:00 04:33:00 must be 28:33:00<o></o>
A7: 2010-0-02 16:48:00 05:33:00 must be 29:33:00<o></o>
A8: 2010-0-03 10:16:00 23:01:00 must be 47:01:00<o></o>
...<o></o>
I tried different methods such as<o></o>
=INT(A2-A$1)&"days,"&TEXT(MOD(A2-A$1,1)*24,"0.00")&"hours"<o></o>
<o></o>
but it is not useful for me since the results are in text format which can't be used for next step calculations and also bc it gives me d:mm:ss format while I need [h]:mm (days in the form of hours).
<o></o>
Thank you for your help in advance. <o></o>
Elham<o></o>
<o> </o>
<o> </o>

 

[GlennUK]

What are the contents of column A? Is it the time and date? What date is 2010-0-02 supposed to be ( month=0 )?

 

[Samuel Sathiaraj]

If A1 and B1 has the date, then try B1-A1.

Format the target cell (where you write the formula) as below.
Category: Custom
Type : [h]:mm

If you enclose the h in square bracket, it will work even if the duration is more than 24 hours.

 

[Elle_saf]

Hi Glenn.
I used "A" column for naming only. The second column is date, the third is time and the last column is the interval time resulted from IF statement.

A3: 2010-0-01 15:47:00 04:32:00<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Name: Date time interval

Date is 2010-09-02 (month Sep.) 2010-0-02 is the typing mistake.

Tanx.

 

[Elle_saf]

Hi Sam.

I did Custom my target cell as [h]:mm still does not work.

Tanx.

 

[barry houdini]

The second column is date, the third is time .....

If you have dates in column A and times in column B then try this formula in C2 copied down

=A2+B2-A$1-B$1

format C2 as [h]:mm

 

[Elle_saf]

Hi Barry.
Tanx so much. It works.

Elham