Date/Time HELP PLEASE!!!

[TroubleMaker]

I have a report that is given to me on a bi-weekly basis. In this report I need to calculate the time difference in time between cell A1: "1/2/02 2:45:00" & B1: "1/2/02 2:46:00" which in this matter is "00:1:00".

I do not have the option of having these separated into date cell and time cell. As there are over 50,000 lines of this, I would like to automate with a formula.

The closest I have got to solving is the following:
=INT(SUM(A1:B2)/0.04166667)&":" &MINUTE(MOD(SUM(A1:B2),1))&":" &SECOND(MOD(SUM(A1:B2),1))

Now this gives me a value of: 1788389:31:0

I believe the date and time being in the same cell is effecting this. I have tried TIMEVALUE however I get a #VALUE error.

Could someone help me out PLEASE?

 

[Legacy 98055]

Unless I'm missing something here?
Put =B1-A1 in whatever cell and format it as
such HH:MM:SS
Tom

 

[Yogi Anand]

If I understand you correctly, you can use
=B1-A1 and Custom Format it as [h]:mm:ss

 

[Yogi Anand]

Hi Tom:
It is telepathy at work ...

Regards!

 

[TroubleMaker]

Hey,

I have been staring at this too long, thanks for the extra set of eyes!!! This group RoCkS!! I will return the favor in the future.
Thank you for the help!!!

 

[Legacy 98055]

I like yours better
[h]:mm:ss
Tom