Difference in Hours

[Guest]

Hi all,

I have to different Date time values. That are Received Date time value and
Informed Date time Value. Both the values are customised in the format as
follows:
dd.mm.yyyy hh:mm:ss AM/PM

Now, i want to find out the difference in hours between two dates in excel.
I know how to do it in Access, but not getting through in excel the same
formula.

For example Received date is 19.04.2005 11:30:00 AM
and Informed Date is 21.04.2005 12:30:00 PM the result should be 49, that is
in hours.

Any help will be highly appriciated.

Thanks in advance

Karan.

 

[Bob Phillips]

Just multiply the result by 24

=(A7-A6)*24

if you only want hours, no minutes fraction use

=INT((A7-A6)*24)

--

HTH

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

 

[Guest]

Thanks for your valuable help. It works great. But, now i realise that it
should exclude sundays in between two date time value's.

Is it posible to do it, if yes please let me know.

Once again thanks for your tips.

Karan.

 

[Bob Phillips]

Hi Karan,

Try this

=(A7-A6-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A6)&":"&INT(A7))))=1)))*24

--

HTH

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

 

[Bob Phillips]

Fails if A& or A6 is a Sunday, this doesn't

=(A7-A6-IF(WEEKDAY(A6)=1,1-MOD(A6,1),IF(WEEKDAY(A7)=1,MOD(A7,1),SUMPRODUCT(-
-(WEEKDAY(ROW(INDIRECT(INT(A6)&":"&INT(A7))))=1)))))*24

--

HTH

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