Merging separate date and time columns into one

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.

Thank you so much for ANY help you can offer.

Brian

Just add them.

=a1+b1
and format as mm/dd/yy hh:mm:ss



Gadgets wrote:
>
> 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.
>
> Thank you so much for ANY help you can offer.
>
> Brian
>
> --
> Gadgets
> ------------------------------------------------------------------------
> Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
> View this thread: http://www.excelforum.com/showthread...hreadid=564976

--

Dave Peterson

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

Originally Posted by Dave Peterson

Just add them.

=a1+b1
and format as mm/dd/yy hh:mm:ss



Gadgets wrote:
>
> 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.
>
> Thank you so much for ANY help you can offer.
>
> Brian
>
> --
> Gadgets
> ------------------------------------------------------------------------
> Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
> View this thread: http://www.excelforum.com/showthread...hreadid=564976

--

Dave Peterson

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

Originally Posted by Dave Peterson

Just add them.

=a1+b1
and format as mm/dd/yy hh:mm:ss



Gadgets wrote:
>
> 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.
>
> Thank you so much for ANY help you can offer.
>
> Brian
>
> --
> Gadgets
> ------------------------------------------------------------------------
> Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
> View this thread: http://www.excelforum.com/showthread...hreadid=564976

--

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.


Gadgets wrote:
>
> 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
>
> Dave Peterson Wrote:
> > Just add them.
> >
> > =a1+b1
> > and format as mm/dd/yy hh:mm:ss
> >
> >
> >
> > Gadgets wrote:
> > >
> > > 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.
> > >
> > > Thank you so much for ANY help you can offer.
> > >
> > > Brian
> > >
> > > --
> > > Gadgets
> > >
> > ------------------------------------------------------------------------
> > > Gadgets's Profile:
> > http://www.excelforum.com/member.php...o&userid=36784
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=564976
> >
> > --
> >
> > Dave Peterson
>
> --
> Gadgets
> ------------------------------------------------------------------------
> Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
> View this thread: http://www.excelforum.com/showthread...hreadid=564976

--

Dave Peterson

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

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

Thanks, Brian

Originally Posted by 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.


Gadgets wrote:[color=blue]
>
> 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

Dave Peterson