+ Reply to Thread
Results 1 to 2 of 2

Mixed format when downloading timestamps

  1. #1
    Registered User
    Join Date
    11-03-2014
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    43

    Mixed format when downloading timestamps

    Hello Excel master community,

    I am seeking your assistance for an issue being encountered when downloading large data ets (typically 200,000+ rows) with an unusual format in one of the columns.

    This column contains time-stamp data for a particular event and the data provided is usually mm/dd/yyyy hh/hh/hh (AM/PM)

    I have put AM/PM in brackets above as sometimes is is there in the data, and sometime it is not. It does not seem to matter if what time of the day the event being reported occurred, sometime's it is there and other times it is not. I have not been able to identify any pattern on when it is there.

    The problem is I usually need to run this data through text to columns and map the date and hour these activities occurred but the mix of data is making it difficult to get a 'clean' view of the times of these activities.

    I have attached an example of some random time-stamps to illustrate what I am referring to.

    I suspect it is a time/date setting somewhere in my Excel/Windows preferences but I could be wrong, and even if I am right I am unable to locate it.

    Is anyone able to help my convert this row into the below format?

    mm/dd/yyyy hh/mm/ss (24hr e.g. 13:02:55)

    Any help at all much appreciated.

    Kindest regards,
    KW
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Mixed format when downloading timestamps

    I found the easiest way to do this is to bring the file into Power Query and do a Change Type to DateTime and then bring back to Excel. Tried to do conversions in Excel, but to complicated as to the mixed format of your data. PQ recognizes the mixed data immediately and repairs.

    Please Login or Register  to view this content.
    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy mixed text format to another cell using IF statement
    By Woodward4 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2019, 12:45 PM
  2. Search for duplicates in column with mixed format data
    By ExcelInMSExcel in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-29-2017, 01:06 PM
  3. Replies: 9
    Last Post: 12-23-2016, 10:54 PM
  4. Looking up data from mixed format list
    By jscalem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2015, 02:36 PM
  5. [SOLVED] mixed date formats in s/s, how to format
    By cal_chica in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2014, 06:22 PM
  6. Mixed dates that need to all be the same format
    By aigo in forum Excel General
    Replies: 2
    Last Post: 12-06-2013, 06:48 AM
  7. Downloading page format from net
    By johnbest in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-16-2005, 02:40 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