How to get difference in hours
 

I have few thousand rows, which contain arrival date, arrival time, received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

Hi
Try this:
=(C2+D2)-(A2+B2)
and format the result as [hh]

--
Andy.


"Akhilesh Dalia" wrote in message
...
I have few thousand rows, which contain arrival date, arrival time,
received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

If they are all in separate cells

=INT((C1-A1-IF(B1D1,1-B1+D1,-(D1-B1)))*24)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Akhilesh Dalia" wrote in message
...
I have few thousand rows, which contain arrival date, arrival time,
received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

=(C1+D1)-(A1+B1)

and format as [hh]:mm:ss.

Or use:

=((C1+D1)-(A1+B1))*24

and format as General to get a decimal format.

HTH
Jason
Atlanta, GA


"Akhilesh Dalia" wrote:

I have few thousand rows, which contain arrival date, arrival time, received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

datedif()

Dear friends,

Thanks for help, but still I'm not getting correct answer. It may be my
mistake to explain my problem. Let's go through real data as per below.

My A1 column contain 01-Jan-2005, B1 column contain 16:54:28 PM, C1 column
contain 04-Jan-2005 and D1 column contain 09:45:45 AM. Now if you calculate
manually the right answer is 64:51 Hrs. But when I'm applying all your
suggestions and formulas I'm not getting 64:51, but some other figures, which
is not correct. I want machine also to give 64:51 Hrs.

Pleas help again.

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

"Akhilesh Dalia" wrote:

I have few thousand rows, which contain arrival date, arrival time, received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

You need to use a custom format of [hh]:mm for the result, I get 64:51 using
your example
Btw, no need to use AM/PM since you use 24 hour time (16:54:28) Btw, Andy
told you to use [hh] format

--
Regards,

Peo Sjoblom


"Akhilesh Dalia" wrote in message
...
Dear friends,

Thanks for help, but still I'm not getting correct answer. It may be my
mistake to explain my problem. Let's go through real data as per below.

My A1 column contain 01-Jan-2005, B1 column contain 16:54:28 PM, C1 column
contain 04-Jan-2005 and D1 column contain 09:45:45 AM. Now if you
calculate
manually the right answer is 64:51 Hrs. But when I'm applying all your
suggestions and formulas I'm not getting 64:51, but some other figures,
which
is not correct. I want machine also to give 64:51 Hrs.

Pleas help again.

Akhilesh Dalia
akhileshdalia<athotmail<dotcom

"Akhilesh Dalia" wrote:

I have few thousand rows, which contain arrival date, arrival time,
received
date & arrival time. I want to get difference in hours, what is the
difference between arrival and received.

Few examples
A B C D
01/Jan/2005 12:14:29 PM 02/Jan/2005 9:30:00 AM

based on above data in E column I want difference between date & time in
total hour.

Thanks for spent your time on this question.

Regards

Akhilesh Dalia
akhileshdalia<athotmail<dotcom