+ Reply to Thread
Results 1 to 4 of 4

formatting date and time

  1. #1
    Registered User
    Join Date
    02-20-2018
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    38

    formatting date and time

    I have a column that with date and time that was automatically entered with no space in between the date and time (2018-08-0812:15:38.156). This seems to make it impossible to format the date using format cells. Just wanted to see if any of you know how I can change this column to have a space in between the date and time without going through every cell, thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,531

    Re: formatting date and time

    In B2 copied down:

    =DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,11,99))

    Format column B as date and time to your liking.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: formatting date and time

    Try this

    Format Custom, "m/d/yyyy h:mm"
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,531

    Re: formatting date and time

    Doh! Overthinking things again ...

+ 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. [SOLVED] Date / Time formatting
    By glomb175 in forum Excel General
    Replies: 4
    Last Post: 04-06-2018, 09:56 AM
  2. [SOLVED] Conditional Formatting with Date/Time with Date/Time of another cell
    By jnepsa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-20-2017, 10:48 AM
  3. Time and Date Value Formatting
    By MikeRoot in forum Excel General
    Replies: 1
    Last Post: 01-12-2015, 07:25 AM
  4. formatting of date and time
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2014, 03:25 AM
  5. Date and time formatting
    By Mayank Trivedi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-10-2008, 03:01 PM
  6. Date and Time Formatting
    By brook1 in forum Excel General
    Replies: 2
    Last Post: 10-23-2006, 09:25 AM
  7. date and time formatting
    By rantz in forum Excel General
    Replies: 3
    Last Post: 11-28-2005, 02:35 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