Excel Date & Time values problem

K

kr_therock

Hi,
I have to combine 2 columns in Excel into one.The first column ha
dates and the second column consists of the respective time values.Ca
anyone tell me as to how to combine them into one column so that I ca
calculate the time difference.

Illustration:
Col1 Col2 Col3
Col4
1-March2004 9:14 03/01/2004 11:45 ?
1 March2004 23:29 03/02/2004 03:30 ?
2 March2004 00:32 03/02/2004 04:25 ?


Now as you can see I have to subtract Col3 from Col1&2 combined.

Can anyone please help??

Thanks,
The Rock
 
P

Peo Sjoblom

Just add them

=A2+B2

then format as mm/dd/yyyy hh:mm

this assumes that the values are numeric or else
you'll get a values error

finally use

=C2-(A2+B2)

format result custom as [hh]:mm

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
M

Michael Malinsky

Assuming your data is in columns A, B and C and beginning in A1, I used:

C1-(TEXT(A1,"mm/dd/yyyy")&" "&TEXT(B1,"H:mm"))

then formatted the result as [h]:mm:ss

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
P

pikapika13

I found a solution, but I think there's an easier way...but here's m
Frankenstein Version.

Step 1: Format all cells to General
Step 2: Add a column after A,B, and C
Step 3: Enter =LEFT(A1,5) in new Column B
Step 4: Enter =MID(C1,2,7) in new Column D
Step5: Enter into any column after worksheet: =concatenate(B1,D1)

Then subract as usual using correct formats.
If you would like...attach a file and I'll complete it for ya.

Regards, Pik
 
P

pikapika13

I found a solution, but I think there's an easier way...but here's m
Frankenstein Version.

Step 1: Format all cells to General
Step 2: Add a column after A,B, and C
Step 3: Enter =LEFT(A1,5) in new Column B
Step 4: Enter =MID(C1,2,7) in new Column D
Step5: Enter into any column after worksheet: =concatenate(B1,D1)

Then subract as usual using correct formats.
If you would like...attach a file and I'll complete it for ya.

Regards, Pik
 
D

Domenic

kr_therock said:
*Hi,
I have to combine 2 columns in Excel into one.The first column ha
dates and the second column consists of the respective tim
values.Can anyone tell me as to how to combine them into one colum
so that I can calculate the time difference.

Illustration:
Col1 Col2 Col3
Col4
1-March2004 9:14 03/01/2004 11:45 ?
1 March2004 23:29 03/02/2004 03:30 ?
2 March2004 00:32 03/02/2004 04:25 ?


Now as you can see I have to subtract Col3 from Col1&2 combined.

Can anyone please help??

Thanks,
The Rock. *
Click to expand...

Hi,

Assuming that Column A contains your date, Column B contains your time
and Column C contains date and time, try,

=(C1-(A1+B1)+(C1<(A1+B1)))*24

and format as general.

Hope this helps
 
Top