TroubleMaker
New Member
- Joined
- May 11, 2002
- Messages
- 12
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?
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?