+ Reply to Thread
Results 1 to 5 of 5

Excel Not Formatting Date/Time

  1. #1
    Registered User
    Join Date
    01-23-2023
    Location
    london, UK
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    2

    Excel Not Formatting Date/Time

    Hi folks:

    I have a data set where I want to plot (scatter plot) against an X-axis/Time axis but I can't seem to convert one of the columns into a date format (the time is not relevant and can be dropped).


    Any ideas?



    date & time size
    Tue, Nov 8, 2022 04:40:00 AM (EST) 2.1666667




    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Excel Not Formatting Date/Time

    C2:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-23-2023
    Location
    london, UK
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    2

    Re: Excel Not Formatting Date/Time

    Thank you, this is fantastic! I had a fairly complicated =datevalue(mid(a2,13,4), mid(.......) etc. trying to parse out the date. Your find feature is very elegant!

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Excel Not Formatting Date/Time

    mid(a2,6,find("(",a2)-7):
    Extract a substring (date and time) from A2 starting at position #6, stop 7 characters before the "("
    For date only:
    mid(a2,6,find("(",a2)-19)
    Datevalue(...) converts the string to an Excel date serial number

    This one does the same thing:
    =DATEVALUE(REPLACE(REPLACE(A2,1,5,""),13,255,""))

  5. #5
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,276

    Re: Excel Not Formatting Date/Time

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] Create conditional formatting using time / date-time
    By Halidat in forum Excel General
    Replies: 3
    Last Post: 10-13-2021, 11:12 AM
  2. Excel not saving cell and time/date formatting
    By juansalix in forum Excel General
    Replies: 1
    Last Post: 03-12-2019, 09:21 AM
  3. Replies: 0
    Last Post: 06-12-2018, 05:22 AM
  4. [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
  5. [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
  6. Excel Formatting to Date/Time
    By lsm33000 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-27-2013, 09:10 AM
  7. 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

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