+ Reply to Thread
Results 1 to 3 of 3

How do I join separate date and time columns into a single date time column?

  1. 03-24-2016, 10:25 PM #1
    mbvenom6
    mbvenom6 is offline
    Registered User
    Join Date
    03-24-2016
    Location
    tyler, texas
    MS-Off Ver
    2013
    Posts
    3

    How do I join separate date and time columns into a single date time column?

    My data is currently formatted with a separate column date (5/08/2009) and time (11:16:00). I need to combine them into one datetime column that reads like this (5/08/2009 11:16:00). I have tried the using the =A1&" "&B1 formula as well as =concatenate but both give me this number 39941 0.469444444444444. Acceptable formats for the program I am using would be dd/MM/yyyy H:mi or yyyy.MM.dd H:mi:s. I am using 24hr time. Please help. I have over 1400 data entries and really do not have the time to retype them all. Thank you.

    -Mitch
    Reply With QuoteRegister To Reply
  2. 03-24-2016, 10:41 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do I join separate date and time columns into a single date time column?

    Use Sum function

    =SUM(A1,B1)

    Then apply format
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 03-24-2016, 11:49 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: How do I join separate date and time columns into a single date time column?

    What you need to understand about dates and times in excel is...

    a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu Mar 2016) is actually 42453

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So, you can just add them, as AlKey showed

    both give me this number 39941 0.469444444444444.
    That is excels way of trying to show the date and time in general format (or just text, if you concat them)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Convert Unix Date-time stamp to excel date and/or time columns
    By judikz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2015, 05:46 PM
  2. Calculating total time between start date\time and End date\time excluding non business
    By jonashlock in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-15-2015, 07:32 PM
  3. [SOLVED] How do I split the date & time into two separate columns?
    By bananajelly in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-22-2014, 03:26 PM
  4. 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
  5. Formula to Calculate Total time spent between Start Date & Time and End Date & Time
    By Manish_Gupta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  6. Merging separate date and time columns into one
    By Gadgets in forum Excel General
    Replies: 5
    Last Post: 07-26-2006, 04:24 PM
  7. Combining Data from Separate Columns into a Single Date and Time Value
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:23 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1