+ Reply to Thread
Results 1 to 6 of 6

Column of Dates in Inconsistant Formats

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Column of Dates in Inconsistant Formats

    Hi there!

    I have a small database of roughly 1300 lines detailing fire-rescue dispatch calls year-to-date. A number of the columns have both dates and times, one for time recieved, one for time dispatched, one for time on scene. Now for some reason the reports that the system produces provide these dates in different formats (sometimes two formats for a single column).

    Example:

    Some of the cells in these columnswill have a numeric date/time code such as: 41275.04 which was set by default to de displayed in a custom format as 1/01/2013 1:00:00 AM. And then some of the cells will have simply text such as 13/01/2013 01:10.

    I am attempting to produce a number of metrics and measures from this data and have started by building a filtered dataset which uses simple index/match functions to populate the carryover columns, however I would like to breakdown these date columns into a column with the date of each call and then merely the time stamps for all the others. I cannot for the life of me figure out how to break these missmatched date formats apart without somehow standardizing them. Because of the discrepancies I cannot run a left/find/index/match function to pull the first half or second half of the line as I had hoped.


    Any help is greatly appreciated.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Column of Dates in Inconsistant Formats

    It would be easier to advise if you shared a workbook with the column of dates...
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Column of Dates in Inconsistant Formats

    See attatched.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,885

    Re: Column of Dates in Inconsistant Formats

    the cells with numbers like 41275.63 need to be formated to be the same as the others.

    The other problem is a regional setting thing. You have a "date" like 13/01/2013 01:10 and excel is trying to read it as mm/dd/yyyy. We can fix that with something like...
    =DATE(MID(A44,7,4),MID(A44,4,2),LEFT(A44,2))
    we can add in the time if you need it?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Column of Dates in Inconsistant Formats

    You can use this formula to deal with each cell as a date, rather than as text:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or as text, rather than a date:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then you can process as you want.

  6. #6
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Column of Dates in Inconsistant Formats

    Absolutly brilliant. Can't thank you enough Olly. I've been stuck on that for a few hours.

+ 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. Formats dates ok 1/1 to 1/12 but not 1/13 to 1/31
    By jpietrowiak in forum Excel General
    Replies: 1
    Last Post: 08-28-2011, 12:40 PM
  2. [SOLVED] I can't find why my formula is inconsistant - please help
    By Kath in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2006, 09:25 AM
  3. [SOLVED] Inconsistant Formula
    By nastech in forum Excel General
    Replies: 3
    Last Post: 03-03-2006, 12:55 AM
  4. Copying formats - column widths, formats, outlining to worksheets
    By DavidBr318 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2005, 09:05 AM
  5. Inconsistant calculations
    By Kulwant in forum Excel General
    Replies: 0
    Last Post: 04-01-2005, 04:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1