+ Reply to Thread
Results 1 to 5 of 5

format date and time column

Hybrid View

  1. 11-04-2015, 05:11 PM #1
    freddie1
    freddie1 is offline
    Registered User
    Join Date
    09-17-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    21

    format date and time column

    Hi all,

    I have a column of date with time,but I convert it to just a date format.

    ie the column currently looks like the following:

    Open Time
2015.10.27 20:00:43
2015.10.28 18:01:39
2015.10.28 18:02:34
2015.10.28 18:03:44
2015.10.28 18:07:03
2015.11.02 12:45:39
2015.11.02 13:40:55
2015.11.02 14:49:23
    I want to have another column with just the date as follows:

    Open Time			Date
2015.10.27 20:00:43		27/10/2015
2015.10.28 18:01:39		28/10/2015
2015.10.28 18:02:34		28/10/2015
2015.10.28 18:03:44		28/10/2015
2015.10.28 18:07:03		28/10/2015
2015.11.02 12:45:39		02/11/2015
2015.11.02 13:40:55		02/11/2015
2015.11.02 14:49:23		02/11/2015
    What formula can I use to achieve this?

    Thanks.
    Reply With QuoteRegister To Reply
  2. 11-04-2015, 05:16 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,698

    Re: format date and time column

    Assume data is in Column A and is in Date format and not text then in column B =Int(A1) and copy down. Format as necessary.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 11-04-2015, 05:20 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,082

    Re: format date and time column

    Formula: copy to clipboard 
    =DATEVALUE(SUBSTITUTE(LEFT(A2,FIND(" ",A2)-1),".","/"))
    formatted as date

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 11-04-2015, 06:51 PM #4
    freddie1
    freddie1 is offline
    Registered User
    Join Date
    09-17-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    21

    Re: format date and time column

    Thanks both for taking the time to respond. I tried your way Alan but it didn't work for me even when I formatted the column A to date.

    Thanks Trevor....worked a treat.
    Reply With QuoteRegister To Reply
  5. 11-04-2015, 07:00 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,082

    Re: format date and time column

    You're welcome. Thanks for the rep.

    Looks to me as though the "dates and times" just look like dates and times but are, in fact, text containing numbers which, visually, you might consider to be dates and times ... but excel doesn't.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    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. Convert a date/time format to a date format
    By LITTLETON in forum Excel General
    Replies: 2
    Last Post: 09-23-2013, 10:43 PM
  2. modify date time cell to an excel format date time
    By rileyp in forum Excel General
    Replies: 12
    Last Post: 03-24-2013, 08:39 PM
  3. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  4. [SOLVED] How do I convert imported date/time data to date/time format?
    By andykent99 in forum Excel General
    Replies: 4
    Last Post: 01-03-2013, 07:47 AM
  5. [SOLVED] Format FIRST incidence of new date in column IGNORING time
    By tradersteve in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2012, 10:13 AM
  6. Excel : Convert data in column to limited row (time and date format)
    By hooi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2011, 07:21 PM
  7. Column Range with date Format MM/DD/YYYY independate of sys date Format
    By gaursh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 03:31 AM
  8. [SOLVED] Remove time from a date and time field? Format removes the displa.
    By oaoboc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-16-2005, 03:35 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