Results 1 to 4 of 4

Thread: Date Format in Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Date Format in Excel

    Hi Everyone

    I have a file with a date/Time format that I am having some issues with. I have tried correcting this using both of the formulas that work with dates.

    The only way that I have found to work with it is to run text to columns and then build the date up from each of the columns that this splits in to.

    I have also attempted a mixture of Left, Mid & Right however, this has not worked either.

    I would like either a formula or a Macro that is able to reference the data and put it in to a format that I am able to work with. The date information that I am working with looks like this "6/27/2019 7:53:16 AM".

    I have tried to attache a file however, have been unable to.

    If anything is unclear drop me a line.

    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,160
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Date Format in Excel

    =LEFT(A1,FIND(" ",A1)-1)+0

    +0 forces the result into a number which is an Excel date, without the +0 the result would just be a string of characters.

    You can't attach files on this site.

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,673
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date Format in Excel

    Your sample Date/Time is good and does not demonstrate a problem.

    "...and put it in to a format that I am able to work with." How are you trying to work with the date/time serial number?
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Date Format in Excel

    You can convert the date/time into 2 columns, one date and one time using Data>Text to columns.

    Use Fixed width, put a break between the date and time, use Date for the column data format for both columns and specify the MDY format for the date column.
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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