Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cumulative time in minutes (Excel 2000)

    Subject edited by HansV to help in future searches

    Hi,

    I am new to this web site and could really use your help. You people couldn't have come more recommended than this!
    I have two columns, one with date and time (I also have date and time in two separate columns if that would be easier) and flow rate on the other. I need to convert the date and time column into minutes (From a specified row T = 0 min. than T= 30 min. etc...- by 30 minutes increment). The other catch is that I need the data and time column to reset itself (back to T= 0 min.) every time the flow rate in the second column equals zero. The first two columns in the example are the original data and the third column is what I am trying to obtain. Any ideas?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative time in minutes (Excel 2000)

    Welcome to Woody's Lounge!

    This can no doubt be done with one formula, but in the attached version of your workbook I've used an intermediate formula.
    D3 and E3 both contain 0 as start values.
    D4 contains the formula =(A4-A3)*24*60. After entering this formula, you'll have to clear the formatting of this cell or set the number format to General.
    This formula calculates the difference between the times in A4 and the cell above it. Since Excel computes times as fractions of 1 day, the result is multiplied with 24 (hours in a day) * 60 (minutes in an hour).
    E4 contains the formula =IF(B4=0,0,D4+E3).
    This formula calculates the cumulative time, resetting it to 0 if the value in column B is 0.
    You can fill down the formulas in D4 and E4 as far as needed. If you wish, you can hide column D.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative time in minutes (Excel 2000)

    Didn't you mean

    =(B4<>0)*((A4-A3)*24*60+C3)

    (You probably tested in column E)

  4. #4
    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: Cumulative time in minutes (Excel 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Yes, you are correct and even understand where the error came from.

    I tested in col E so I could compare to the correct values in col C and forgot to correct for this.

    I will edit my response.

    Steve

  5. #5
    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: Cumulative time in minutes (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 03-Jul-08 06:10. Corrected mistake in formula. Thanks Hans.)</P>In C3: 0
    In C4:
    =(B4<>0)*((A4-A3)*24*60+C3)

    Copy C4 down the column

    [Note: Excel stores dates/times in units of days. The 24 converts to hours and 60 converts to minutes]

    Steve

  6. #6
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumulative time in minutes (Excel 2000)

    Good afternoon Hans and Steve,

    I can't use Steve's answer because the "C" column is an example of the result I was trying to obtain. However, I tried Hans solution and it works fine. Thank you. It was very clever to use the "IF" function.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative time in minutes (Excel 2000)

    Steve's formula would work as well - I have attached your workbook with Steve's formula adapted to be used in column D.
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumulative time in minutes (Excel 2000)

    WOW! Altough I don't understand the formula, it actually works! I am definitely reading the Excel manual to understand the details. Thank you both, you have no idea how this is going to make my work easier. I only have a year and half worth of data (by half hour increment) to process!

    Cheers!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative time in minutes (Excel 2000)

    The essential point of Steve's formula is that B4<>0 evaluates to TRUE (if B4 is not equal to 0) or to FALSE (if B4 equals 0), and that TRUE = 1 and FALSE = 0.

    The second part (A4-A3)*24*60+D3 is basically the same as what I used - it adds the number of minutes to the previous running total. But if B4 equals 0, this value is muliplied with 0, effectivel resetting the running sum to 0.

Posting Permissions

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