+ Reply to Thread
Results 1 to 12 of 12

Convert UNIX timestamps to XL and remove squares from a string of data in a column

  1. 02-16-2011, 05:08 PM #1
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Thumbs up Convert UNIX timestamps to XL and remove squares from a string of data in a column

    Hello Experts!

    I have an excel 2003 workbook with a column of data imported from UNIX that contains UNIX date/timestamps as well annoying 'squares' which represent spaces. Is there a macro or formula that can reformat this data to a more friendly/readable format (mm/dd/yyyy and replace the squares with spaces). Here is an example of what is contained in a single cell in this column (Column K):

    1294698665herambh.sharmalaApproved -- as alternate for chamner1294701745tami.fordhanApproved1294755258joe.smithApproved1294755258Application ServiceProcess Approved

    Any help would be greatly appreciated.

    S.
    Last edited by symaxf; 02-22-2011 at 07:01 PM. Reason: added prefix
    Reply With QuoteRegister To Reply
  2. 02-18-2011, 07:10 PM #2
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Just bumping this up to see if someone out there can point me in the right direction before I revert to plan B -S
    Reply With QuoteRegister To Reply
  3. 02-19-2011, 03:19 AM #3
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Lack of response generally indicates lack of clarity / info.

    I'd suggest posting a sample file which includes both "before" and "after".
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  4. 02-21-2011, 01:38 AM #4
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Thanks DonkeyOte for the response. Here is a sample before/after file. I would like the data in the "Audit" column modified/formatted formatted like the 'after' sample with converted UNIX date/times to XL date/times and with a new line (Alt +Enter) before each date. -S.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-21-2011, 05:08 AM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    One possibility:

    Please Login or Register  to view this content.
    some of your UNIX conversions in the "After" seem awry.
    Last edited by DonkeyOte; 02-21-2011 at 05:17 AM. Reason: misplaced line
    Reply With QuoteRegister To Reply
  6. 02-21-2011, 08:16 AM #6
    watersev
    watersev is offline
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    please check attachment, run code "test".

    PS. In your example there are mistakes in Sheet "after" dates result
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-21-2011, 12:13 PM #7
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Thank you both so very much!

    waterserv, I received a "type mismatch" on the following line of code: Ndate = DateAdd("d", x \ 86400, "01.01.1970")
    Donkey Ote, Looks like your code did the trick!
    (I've attached an updated sample containing both macros and results)

    Cheers!

    S.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-21-2011, 02:16 PM #8
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    I've used this macro on a more representative worksheet and am getting a "type mismatch" error this line of code 
    Please Login or Register  to view this content.
    The only thing I changed was column "D" to "K" and I'm not smart enough to figure out why. Please advise. Thanks, S.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-22-2011, 03:58 AM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    The issue stems from the resulting strings in the Array exceeding 255 chars which impacts use of Index (and Transpose)

    Below is a less desirable approach given iterative load from VBE to native XL but it should work.
    Given the above I have added some app level toggles to "optimise" performance (basic approach)

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 02-22-2011, 06:05 PM #10
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Thank you - I just ran the new macro and received a "compile error: variable not defined"' on the following line: 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 02-22-2011, 06:36 PM #11
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    Sorry, was rushing a little earlier and coded (in part) on the fly... add following variable declaration

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 02-22-2011, 07:07 PM #12
    symaxf
    symaxf is offline
    Registered User
    Join Date
    08-19-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel Mac 2011
    Posts
    59

    Re: Convert UNIX timestamps to XL and remove squares from a string of data in a colum

    DonkeyOte, you have exceptional intellectual and technical ability my friend. If you ever should make it down to Arizona drop me a line and I'll buy you a drink (or three). You'll feel real at home here, lots of 4 legged friends with saddles! Thanks again!

    Cheers!

    S.
    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)

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