+ Reply to Thread
Results 1 to 7 of 7

Convert text to hours/timestamp

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    Convert text to hours/timestamp

    Hi everyone,

    I have spreadsheet with a lot of observations, taken every 30th minutes (48 observations during a day).
    However, the data comes in a quite annoying format. Instead of writing dd:mm:yyyy hh:mm: (e.g. 01.01.2012 10:00), the data comes as a date text stamp!

    It looks like this where HH stands for every half hour (so HH=1=30min). Also, you can see how the timestamps are done simoultaniously for several types of units.
    Capture.PNG


    HOW do I convert this into something reasonable that Excel understands?

    Thanks in advance

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Convert text to hours/timestamp

    Try this code on that sheet.

    Please Login or Register  to view this content.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Convert text to hours/timestamp

    I just relooked at the png file and it also has integers 3,4 and 5. What do they stand for?

  4. #4
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Convert text to hours/timestamp

    Each HH stands for half an hour.. so HH=5=(30min x 5)=2,5 hours...
    Its stupid, yes.. I know

  5. #5
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Convert text to hours/timestamp

    I am going to bed, but I will take a wild stab that each value of 1 = 30mins time period. So a value of 3 will equal 90mins and avalue of 4 will = 120 mins or 2 hours.

    If so the below code is what you need.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-08-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Convert text to hours/timestamp

    Where do you implement that code...? (maybe a newbie question, sorry!)

  7. #7
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Convert text to hours/timestamp

    Can you post a sample workbook? Posting the code in a standard module is what you need to do , but since no sheets where referenced it has the chance of not doing what it is supposed to do.

+ 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