Date/Time HELP PLEASE!!!

TroubleMaker · May 12th, 2002, 11:40 AM

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?

Tom Schreiner · May 12th, 2002, 11:49 AM

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

Yogi Anand · May 12th, 2002, 11:51 AM

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

Yogi Anand · May 12th, 2002, 11:54 AM

Hi Tom:
It is telepathy at work ...

Regards!

TroubleMaker · May 12th, 2002, 11:57 AM

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!!!

Tom Schreiner · May 12th, 2002, 12:00 PM

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

May 12th, 2002, 11:51 AM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	If I understand you correctly, you can use =B1-A1 and Custom Format it as [h]:mm:ss __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 12th, 2002, 11:40 AM	#1
TroubleMaker New Member Join Date: May 2002 Location: Dan Posts: 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?

May 12th, 2002, 11:49 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Unless I'm missing something here? Put =B1-A1 in whatever cell and format it as such HH:MM:SS Tom

May 12th, 2002, 11:54 AM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Tom: It is telepathy at work ... Regards!

May 12th, 2002, 11:57 AM	#5
TroubleMaker New Member Join Date: May 2002 Location: Dan Posts: 12	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!!!

May 12th, 2002, 12:00 PM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I like yours better [h]:mm:ss Tom