+ Reply to Thread
Results 1 to 2 of 2

Need to Transpose Irregular Time Intervals into Regular Time Intervals

  1. #1
    Registered User
    Join Date
    06-24-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need to Transpose Irregular Time Intervals into Regular Time Intervals

    Hello,

    I am having difficulty transposing data that is recorded in irregular time intervals into a separate sheet which puts the data into 1-min or 5-min intervals. The data represents the time that a piece of machinery turns "on" or "off", designated by a "1" or "0" respectively.

    For example, the data I have looks like:

    A B C
    Date Time On/Off
    1/12 12:00:00 1
    1/12 12:03:00 0
    1/12 12:07:00 1

    And I need to change it into:

    A B C
    Date Time On/Off
    1/12 12:00:00 1
    1/12 12:01:00 1
    1/12 12:02:00 1
    1/12 12:03:00 0
    1/12 12:04:00 0
    1/12 12:05:00 0
    1/12 12:06:00 0
    1/12 12:07:00 1

    etc.

    I'm not sure if a Macro is needed for this application, or if there is another excel trick to perform this task. Any help will be appreciated, thank you.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need to Transpose Irregular Time Intervals into Regular Time Intervals

    Hi AlexJT and welcome to the forum,

    Find the attached where:
    Col A is all minutes with a formula that is just pulled down from A3.
    I used a VLookup formula with a TRUE for approximate to get the results in Col B

    Hope this is what you wanted.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ 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