+ Reply to Thread
Results 1 to 8 of 8

combine separate date and time columns into a single column

  1. #1
    Registered User
    Join Date
    05-11-2018
    Location
    davis, ca
    MS-Off Ver
    office 365
    Posts
    14

    combine separate date and time columns into a single column

    I have many worksheets showing date and time in separate columns. How can I merge date and time into one column separated by a space, '12/22/17 13:30:00'. I need to do this for many worksheets containing thousands of rows of data. Can I do this with a single command in each worksheet? Thanks for helping.

  2. #2
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    655

    Re: combine separate date and time columns into a single column

    Try the SUM function, then apply date format mm/dd/yyyy hh:mm

  3. #3
    Registered User
    Join Date
    05-11-2018
    Location
    davis, ca
    MS-Off Ver
    office 365
    Posts
    14

    Re: combine separate date and time columns into a single column

    Yes that works if I do it individually in column C of each row, but when I copy and paste the formula, SUM($a1+$b1),
    into other rows it returns the time and date sum from row 1.
    Excel doesn't seem to recognize the mixed reference formula. I've never seen this happen when doing straight math in Excel.
    Formulas shouldn't work differently with dates and times should they?

  4. #4
    Registered User
    Join Date
    05-11-2018
    Location
    davis, ca
    MS-Off Ver
    office 365
    Posts
    14

    Re: combine separate date and time columns into a single column

    Wow. I just realized that the although Excel indicates it is recognizing the mixed reference formula from row 1 column C that i special pasted (formula only) into column C of all the other rows the value returned is the same as that in column C row 1. Excel has not used the formula as written and is not incrementing the data from rows 2-2000. I guess there is no way to attach a sample sheet to this message to prove I'm not crazy.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: combine separate date and time columns into a single column

    By any chance... are calculation options set as Manual??

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  6. #6
    Registered User
    Join Date
    05-11-2018
    Location
    davis, ca
    MS-Off Ver
    office 365
    Posts
    14

    Re: combine separate date and time columns into a single column

    Thanks to all for great suggestions. As it turned out I got tired, turned off the computer and went to bed. Woke up this morning, turned on the computer, and everything worked fine. Great suggestions though. I will remember the bit about manual/automatic calculations.

  7. #7
    Registered User
    Join Date
    05-11-2018
    Location
    davis, ca
    MS-Off Ver
    office 365
    Posts
    14

    Re: combine separate date and time columns into a single column

    How does a person mark a thread as solved.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,690

    Re: combine separate date and time columns into a single column

    Use the thread tools drop down in the ribbon above your first post.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 10-02-2017, 11:24 AM
  2. Replies: 2
    Last Post: 03-24-2016, 11:49 PM
  3. Need to separate date/time from a single cell and create a date/time chart
    By slicksilver79 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-19-2014, 09:48 PM
  4. [SOLVED] Combine Date and Time into one single Cell
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2012, 03:55 PM
  5. How to combine separate columns in to single range
    By is49460 in forum Excel General
    Replies: 5
    Last Post: 05-20-2011, 10:45 AM
  6. Replies: 1
    Last Post: 12-08-2010, 09:59 AM
  7. Replies: 0
    Last Post: 08-23-2005, 12:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1