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<at>hotmail<dot>com

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

--
Andy.


"Akhilesh Dalia" <AkhileshDalia@discussions.microsoft.com> wrote in message
news:2A0E1D0C-0575-481A-BF0C-1FA2488FE0DA@microsoft.com...
>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<at>hotmail<dot>com

If they are all in separate cells

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

--

HTH

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


"Akhilesh Dalia" <AkhileshDalia@discussions.microsoft.com> wrote in message
news:2A0E1D0C-0575-481A-BF0C-1FA2488FE0DA@microsoft.com...
> 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<at>hotmail<dot>com

=(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<at>hotmail<dot>com

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<at>hotmail<dot>com

"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<at>hotmail<dot>com

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" <AkhileshDalia@discussions.microsoft.com> wrote in message
news:60DC08FB-79B9-475E-980E-12BFC1C1C1C5@microsoft.com...
> 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<at>hotmail<dot>com
>
> "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<at>hotmail<dot>com