Results 1 to 9 of 9

20080702, 08:19 #1
 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?

20080702, 08:26 #2
 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 =(A4A3)*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.

20080702, 23:01 #3
 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)*((A4A3)*24*60+C3)
(You probably tested in column E)

20080703, 00:09 #4
 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

20080703, 00:10 #5
 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 03Jul08 06:10. Corrected mistake in formula. Thanks Hans.)</P>In C3: 0
In C4:
=(B4<>0)*((A4A3)*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

20080703, 07:45 #6
 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.

20080703, 08:13 #7
 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.

20080703, 08:22 #8
 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!

20080703, 08:42 #9
 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 (A4A3)*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.