Merging separate date and time columns into one

G

Gadgets

Hi everyone,

I have spent the better part of the last two days trying all kinds
of ways to merge two columns together with next to no success. I'm sure
this help is buried in these forums somewhere but a quick search found
nothing, and I am tired of trying functions and formulas that do either
nothing at all, or do the wrong thing. Here is what I am trying to do:

Currently I have two separate columns in a excel sheet. One contains
the date (ie. 07/25/06) and the other contains a time (ie. 20:18:00).

I would like to merge them into one to look like this:

07/25/06 20:18:00 seconds aren't actually needed but excel 2003 seems
to want them there.

Can anyone help me out with this before I go crazy?! I am NOT a regular
excel user so please spell this out clearly if you can. I have over 400
rows that need to be changed manually if this can't be done
automatically.

In case you are wondering..... my wife's boss changed her mind after
all this time of using separate date time columns. :rolleyes:

Thank you so much for ANY help you can offer.

Brian
 
G

Gadgets

Hi Dave,

I have tried that so many times today. It doesn't work. All I end up
getting is #VALUE! in the cell. I think, what might be the problem is
excel 2003 itself. The format I am using in the TIME column is hh:mm:ss
because that is the only option I have other then formatting to AM or
PM. However, if I try to format the column where the resulting merge
would be, I have only the options of hh:mm as far as the time half of
it goes... no spot for the seconds. I have a feeling this is where the
problem could be.... what do you think? And, how does one go about
fixing that if it is. I have used excel 2000 in the past and it had
more time options it had the hh:mm OR the hh:mm:ss formats available.
Almost makes me wish I hadn't upgraded a while back. :)

Thanks for the help!!
Brian
 
G

Gadgets

OK wait!! I figured out what is going on!!

Excel will not properly calculate anything (or at least what I am
doing) when using the date format of mm/dd/yy however, if I change the
dates to dd/mm/yy it will add (merge) the time and date columns
perfectly doing what you said (=a1+b1). I decided to play a bit seeing
as I had seen this simple formula mention several times today. What a
frustrating time I have had, and all because excel doesn't understand
one of it's own formats!

Thanks for the help, I think I'm on my way now. :)

Brian
 
D

Dave Peterson

It sounds like your dates aren't really dates--just plain old text.

You may be able to select the offending column of cells and do Data|Text to
columns and choose dmy (the same order as your data) as the type of field. You
can always format the cell later to make it look the way you want.
 
G

Gadgets

Well the columns were formatted to the mm/dd/yy format before I eve
started trying any of this, but hey you could certainly be right.

I have found something interesting now that function is working. I ha
formatted the date in the original "date only" column as mm/mm/yy when
first started, then when I found the date format not calculating
switched the format to dd/mm/yy, however today as I have continue
adding formulas for different purposes on the sheet I noticed not al
dates changed from mm/dd/yy to the new dd/mm/yy during the bulk forma
change. I have since manually corrected them, but I am curious why onl
about a half dozen didn't change out of 393 entries when the rest did.
have learned SO MUCH about Excel in the past week, my head hurts. :)

Thanks, Brian

Dave said:
It sounds like your dates aren't really dates--just plain old text.

You may be able to select the offending column of cells and d
Data|Text to
columns and choose dmy (the same order as your data) as the type o
field. You
can always format the cell later to make it look the way you want.
Click to expand...
 
Top