+ Reply to Thread
Results 1 to 11 of 11

Mixed types and date conversions, including time stamps

  1. #1
    Registered User
    Join Date
    04-07-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Mixed types and date conversions, including time stamps

    Hi,
    I have a string of time stamped dates. Some are stored as TYPE=1 and some as TYPE=2. I need to convert these to a format of 31-Mar-11. I have successfully done it with all of the TYPE 2 values, but for some reason the TYPE 1 values are reverting to a DDMMYY version and hence, whenever the day is below 12, Mar 12 2011 will revert to 3-Dec-11. It's driving me insane.

    On top of it, all of the raw dates that come in are formatted as MMDDYY, but some (and this isn't consistent), are coming in as "03312011 <timestamp>" and some as "3312011 <timestamp>". I've figured out this part but thinking it might play into my next set of formulas to convert the various types. please help! Below is an example of the formulas I'm using:

    Raw data eg:
    40547.5375
    03/31/2011 22:51
    3/31/2011 22:33

    then i manipulate it with this formula:
    =IF(MID(A12,2,1)="/",CONCATENATE("0",A12),A12)

    OUTPUT:
    4-Jan-11
    03/31/2011 22:51
    03/31/2011 22:33


    and then i maniupate that out with this formula:
    =IF(TYPE(A12)=2,DATEVALUE(MID(B12,4,2) &"/"& LEFT(B12,2)&"/"&MID(B12,7,4)),B12)

    OUTPUT:
    4-Jan-11
    31-Mar-11
    31-Mar-11
    ** as you can see, the top date should be April 1, 2011, and not Jan 4, 2011.

    This is driving me crazy and I'm sure there has to be a better way than separating out the time stamp with text to columns (manual), then (manual again) using text to columns to convert to MMDDYY.

    Please help!

    Sincerely,
    frustrated in syntax
    Last edited by rharro; 04-08-2011 at 11:46 AM.

  2. #2
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    Your approach works fine for me.

    HTML Code: 

    I suspect it relates to your Regional settings and that you're getting cells containing TEXT which look to be in an American style date format.

    Works for me because I've got UK settings and the dates are converted to UK style ... at least that's what I think it is.

    Regards

  3. #3
    Registered User
    Join Date
    04-07-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Mixed types and date conversions, including time stamps

    Hi!
    thanks for your feedback.

    I see that it did work for you for the March dates, but were you able to convert the raw data containing the April 1, 2011 date? That's the one I'm struggling with.

    It outputs as 4-Jan-2011 rather than 1-Apr-2011...

    I just didn't see that in the example you depicted below.

    Thanks for your comment, and I will look into the regional settings.

    I appreciate your feedback!

  4. #4
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    HTML Code: 
    The number you have posted is the day number for 4 January 2011, as shown above.

    The above representations are a) a typed in date, b) the day number for that date, and c) a formatted representation of the date: dd/mm/yyyy dddd, dd mmmm, yyyy

    I think your headache will continue if you are trying to convert that number into 1 April ... or am I just beginning to see the significance of the question? ;-)

    Regards

  5. #5
    Registered User
    Join Date
    04-07-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Mixed types and date conversions, including time stamps

    Ok, now I'm getting even more confused,

    let's just take this example...

    40850.74722

    when i simply format the cell to a date (US english) it outputs as:
    3-Nov-2011

    The forumla bar reads:
    03/11/2011 5:56:00 PM

    I know for a fact that this date cannot be coming out of our system as Nov 3, 3011 as this date hasn't occurred yet. I believe that the this (40850.74722) is storing this date (03/11/2011 5:56:00 PM), which is the correct mmddyyy format i want, but for some reason, it reverts to ddmmyyy when I attempt to format it.

    Thanks again for your input!

  6. #6
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    HTML Code: 

    Regards

  7. #7
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    Where does your raw data originate from?

    Is it a text file?

    Can you upload a sample?

  8. #8
    Registered User
    Join Date
    04-07-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Mixed types and date conversions, including time stamps

    Hey there!
    I work for a big global business and they have developed this internally - hence the inconsistencies. Below is an example of the raw data that I have to deal with...

    40667.68403
    40667.56875
    40667.5625
    40667.55417
    40667.43194
    40637.55694
    40637.50556
    40637.45486
    40547.73264
    40547.70069
    40547.5375
    03/31/2011 22:51
    3/31/2011 22:33
    3/31/2011 22:22
    3/31/2011 21:57
    3/31/2011 21:04
    3/31/2011 19:59
    3/31/2011 19:46
    3/31/2011 19:14
    3/31/2011 17:23
    3/31/2011 17:12
    3/31/2011 17:06
    3/31/2011 16:57
    3/31/2011 10:50
    3/31/2011 10:08
    3/31/2011 10:00
    3/31/2011 9:50
    3/31/2011 9:41
    3/30/2011 18:41
    3/29/2011 15:33
    3/29/2011 13:11
    3/29/2011 12:56
    3/29/2011 11:03
    3/29/2011 10:45
    3/28/2011 16:37
    3/24/2011 16:13
    3/23/2011 12:58
    3/22/2011 17:10
    3/22/2011 14:38
    3/22/2011 14:30
    3/22/2011 9:11
    3/22/2011 9:04
    3/22/2011 8:47
    3/21/2011 16:52
    3/21/2011 14:08
    3/18/2011 13:40
    3/16/2011 17:01
    3/16/2011 16:53
    3/15/2011 12:32
    3/15/2011 11:59
    3/15/2011 11:25
    3/14/2011 15:07
    3/14/2011 10:48
    3/14/2011 10:17
    40850.74722
    40850.74444
    40850.64097
    40850.625
    40850.60139
    40850.60069
    40819.69028
    40789.69236
    40789.63958
    40789.61597
    40789.59167
    40789.42708
    40789.42639
    40727.49653
    40636.76181
    40636.53264
    40605.61736
    40605.41597
    40577.85833
    40577.65208
    40577.64861
    40577.64236
    40577.58889
    40577.58125
    40577.54792
    40577.40347
    40546.59514
    40546.47292
    40546.43333
    2/28/2011 15:34
    2/28/2011 13:28
    2/28/2011 13:06
    2/25/2011 13:33
    2/24/2011 19:00
    2/24/2011 14:00
    2/24/2011 13:57

    Thanks!
    Rebecca

  9. #9
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    Hi Rebecca

    See the attached workbook with your dates in them. I think you'll find that you do have November dates. Whether you should have or not is another matter.

    I used:

    =IF(TYPE(A2)=1,INT(A2),DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),IF(FIND("/",A2)=3,LEFT(A2,2),LEFT(A2,1)),MID(A2,FIND("/",A2)+1,2)))

    to check and convert the dates. See the attached workbook.


    Regards
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-07-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Mixed types and date conversions, including time stamps

    Thanks so much for all of your help. You are right. The long and short of it is that the data is corrupt. I'm going to have to make another work around that won't be as automated, but will fill the gap.... bugger it, I might tell my business that it's not possible unless they fix the garbage data!

    Thanks again,
    rharro

  11. #11
    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
    37,357

    Re: Mixed types and date conversions, including time stamps

    You're welcome. At least now you know where the problem lies.

    It's interesting that the dates that are in September, October and November are the ones in numeric format and look as though they must have come out of another Excel workbook. You can almost excuse American style dates that need "manipulating".

    All the best telling the business what they can do with their data ;-)

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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