Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Excel VBA Time Calculation (Excel 2002 SP2)

    I've been working on a routine that looks at data files that include time readings. These time readings are taken at irregular intervals and there is a lot more data than we want to look at currently. So we are looking at the readings that are taken every 5 minutes. The challenge is to identify the 5 minute period since we can not look down x number of rows. Nor can I say: match 9:00 am with the data in the file, because there may not have been a reading taken at 9:00 am.
    I have developed 2 strategies for calculating time and I'd be interested in your comments (I'm not looking to have anything fixed - just a discussion on strategy).
    Strategy 1:
    Created a variant variable to hold 300 seconds.
    Loaded the data into an array
    Loop through the array subtracting each time from the previous time reading (making the result absolute) to calculate the elapsed seconds between readings and storing the result in a variant variable.
    When the result is >= 300 seconds, the 5 minute goal has been reached.
    Strategy 2:
    Created a single variable that holds the value of 3.47222222222222E-03 (the serial value of 5 minutes), this the goal time.
    Loaded the data into an array
    Loop through the array subtracting the goal time from each time reading.
    If the result is >=0 the 5 minute goal has been reached.
    All variables holding times have been defined as single.
    Comments:
    In practice Strategy 1 gives me 5 min increments with a minimum value of 5 minutes. Strategy 2 gives a maximum value of 5 minutes. Strategy 2 also seems to avoid the difficult problem of years when creating a calculation with times.

    As I said earlier, I'd be interested in your thoughts on calculating times in VBA.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Are these "time readings" time of day, or are they elaplsed time? If they are elapsed time, what happens at 24:00? If they are time of day, is there also a date? If "All variables holding times have been defined as single," exactly what do these variables contain? Values like Excel keeps Date/Time values? If not, how are the times converted into single precision variables?
    Legare Coleman

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    They are time of day readings. Fortunately, none of the data sets goes over 24 hours. No date is included in the data taken (these are csv files which are imported into Excel).
    I've found that time entered like this 0:12:16 (which apparently is 12:12:16 AM, when viewed in the formula bar) will convert to 0.00851851851851848 when the variable is defined as a single.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    That is a double value (a single can't hold that many digits) not a single, and it is in the format of an Excel date/time value. What happens when you go past midnight? If there is no date, does the time just go down? Or, is there never more than 24 hours of data?

    In my opinion, the best way to accomplish what you want would be to select your first time and add 5 minutes to it and then search for the next value greater than or equal to that. Select that value, add 5 minutes to it and search for the next value greater than or equal to that. Continue on like that. If the time values do cross midnight, then when you add 5 minutes to the current time, check to see if the result is greater than or equal to 1 and if it is subtract 1 and use the result to find the next time.

    If these are
    Legare Coleman

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    a single can't hold that many digits Even expressed as an exponent?
    Fortunately, there is never more than 24 hours of data.
    The problem I kept running into when trying the select time and add 5min to it scenario is that years kept getting in the way (the years 1899 and 1900 respectively). Because even though the year is not visible in the data - apparently it is still there.
    I couldn't think of a strategy to "level" everything to the same year.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    A single has 7 significant digits, a double has 15 sig figs

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Yes, even expressed as an exponent. A single is limited to 7 signigicant digits. A double has 15 significant digits which is what the number you had in your message has.

    I would have to see your code to see how "years kept getting in the way." Excel stores date/time as a double. The fractional part of the double is the fractional part of a day. Therefore, .25 would be 6:00am, .5 would be 12:00pm, and .75 would be 6:00pm, etc. The whole number part is the number of days since 12/30/1899. Therefore, 0.00851851851851848 would be 12:12:16 AM 12/30/1899, 1.00851851851851848 would be 12:12:16 AM 1/1/1900, 2.00851851851851848 would be 12:12:16 AM 1/2/1900. So, it was actually probably the day that was getting in your way, but that day went from 12/30/1899 to 1/1/1900. If your times never exceed 24 hours, then when you add 5 minutes, you can check to see if the result is greater than or equal to 1, and if it is just stop.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Catharine, if you are only working with time intervals, you could strip out days (and therefore years) with the INT function, something like:

    dblTime = dblExcelDateTime - INT(dblExcelDateTime)

    HOWEVER, if your times run between one day and the next you'll get invalid results when you subtract start time from end time, so I think it's better to let the days stand and do the subtraction including them.

    Oh, and FWIW, if you do run over 24 hrs, <post#=333923>post 333923</post#>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    If I understand the "question". A non-VB approach would not be that difficult with formulas and then filtering if desired:

    If you have a heading in A1, the first time in A2 and it goes down the column. (I am "picturing" a parsed dataset from some data acquisition or the like)

    In B2 enter:
    <pre>=A2</pre>


    In B3:
    <pre>=IF(A3-MAX($B$2:B2)>5/24/60,A3,"")</pre>


    Copy/Autofill B3 down the columns. It will have a null for differences <5 min, and put the time for the intervals >=5 mins.

    If you want a list of elapsed times, you can add them in col c. In C2:
    =IF(B2="","",A2-$A$2)

    Copy/Autofill down the column.

    Format the cells as appropriate. you can filter out the "blanks" to get the rows in the desired interval. (which may be copied elsewhere) or You could paste values, select the "blank rows" then delete them, if desired to only have the "Subset" of data.

    Steve

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    That is the clearest explanation I've seen about time!
    Thank you.
    The other curious thing is, if I'm using the wrong variable type - how come I'm getting the desired results?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Hey - there's something to that...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Very clever Steve. Yes your picture of the data is correct.
    I have to say, I never thought about a formula based approach. But what is the 5/24/60 representing?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Date/Time variables are the same as doubles when used in calculations. The only difference is how Excel interprets the result. Therefore, you can do the calculations and get a valid result, but if you display or print the variable without forcing formatting it will not display as a Date/Time. If you are using singles instead of doubles, then you will lose some precision, but for what you are doing the rounding should result in almost the same result (plus or minus one or two seconds).
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    The 5/24/60 represents 5 minutes divided by 24 hours in a day divided by 60 minutes in an hour. That converts the 5 minutes into Excel's internal Date/Time format.
    Legare Coleman

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel VBA Time Calculation (Excel 2002 SP2)

    Thanks!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •