+ Reply to Thread
Results 1 to 11 of 11

Combine Date and Time

  1. 02-08-2018, 08:26 PM #1
    hq7878
    hq7878 is offline
    Registered User
    Join Date
    02-03-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Combine Date and Time

    Could someone please help me combining the date and time into one cell?

    Attached is the spreadsheet.

    Thanks

    timedate.JPG
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-08-2018, 08:32 PM #2
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Combine Date and Time

    Like, in the cell K2:
    Please Login or Register  to view this content.
    And apply the custom format:
    Please Login or Register  to view this content.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
    Reply With QuoteRegister To Reply
  3. 02-08-2018, 08:43 PM #3
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Combine Date and Time

    also you can do that with PowerQuery add-in (for Ex2010 Pro Plus)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-08-2018, 08:52 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Combine Date and Time

    In K2,

    =TEXT(B2, "0000\/00\/00") + TEXT(D2, "00\:00\:00")
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 02-08-2018, 10:51 PM #5
    hq7878
    hq7878 is offline
    Registered User
    Join Date
    02-03-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Date and Time

    Thanks everyone for your help!
    Reply With QuoteRegister To Reply
  6. 02-08-2018, 11:02 PM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Combine Date and Time

    You are welcome

    Have a nice day
    Reply With QuoteRegister To Reply
  7. 02-08-2018, 11:11 PM #7
    hq7878
    hq7878 is offline
    Registered User
    Join Date
    02-03-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Date and Time

    It mostly works but a few kinks. Could someone see what's going on?
    I tried all three ways suggested and running into the same issue.

    File attached. Thanks

    dt2.JPG
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-08-2018, 11:25 PM #8
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Combine Date and Time

    Mybe read this first: Date function, Time function and TimeValue function
    Reply With QuoteRegister To Reply
  9. 02-08-2018, 11:31 PM #9
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: Combine Date and Time

    Use this in K2:

    Please Login or Register  to view this content.
    Quang PT
    Reply With QuoteRegister To Reply
  10. 02-09-2018, 12:05 AM #10
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,625

    Re: Combine Date and Time

    formula 1: =DATE(LEFT(B2,4), MID(B2,5,2), RIGHT(B2,2)) + TIME(LEFT(D2,2), MID(D2,3,2), RIGHT(D2,2))
    formula 2: =TEXT(B2, "0000\/00\/00") + TEXT(D2, "00\:00\:00")
    A)
    Formula #1 assumes a string length of 6 and treats the first two characters as hours, the second two as minutes and the third two as seconds. However, with, e.g. D85=3900 the length is only four characters. The result is an hour value of 39 which is converted to 39 - 24=15, i.e. 3 pm. Notice that since the string is only four characters long, the minutes and seconds are calculated from the same two trailing 00's The result in K85 is: 1/3/2001 3:00 PM.

    Using formula #2 (shg's) the result is 12:39 AM
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  11. 02-09-2018, 01:18 AM #11
    hq7878
    hq7878 is offline
    Registered User
    Join Date
    02-03-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Date and Time

    Okay - thanks again. Working now
    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. How to combine date and time in a cell
    By Terressa in forum Excel General
    Replies: 1
    Last Post: 06-25-2015, 07:36 AM
  2. Date & Time combine
    By thong127 in forum Excel General
    Replies: 9
    Last Post: 03-04-2014, 02:33 PM
  3. Unable to combine Date and Time
    By sajeel in forum Excel General
    Replies: 3
    Last Post: 06-15-2010, 03:18 PM
  4. Combine 2 Date + Time Columns?
    By gkk2001@alltel. in forum Excel General
    Replies: 2
    Last Post: 03-26-2009, 02:49 AM
  5. Combine date and time
    By BN-CD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2006, 11:31 AM
  6. Combine Date & Time Cells
    By Ron Lieber in forum Excel General
    Replies: 2
    Last Post: 01-31-2006, 04:15 PM
  7. combine date from one column with time from another
    By Phyllis Salyers in forum Excel General
    Replies: 1
    Last Post: 08-30-2005, 04:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1