cumulative time in excel

I am trying to get a function that will add time in the format of
hours:minutes. Each cell is the difference between actual time and forecast
time. Therefore sometimes I have a negative value. I cannot add the values if
I have a negative time cell.

example:
C1 is the difference between A1 and B1, lets say it equals :40 (40 minutes)
C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
under forecast)
The totals for column C should equal 1:10 (1 hour 10 minutes) but it doesn't
allow a negative time value.

Any ideas how I can make this work and how I should format the cells????

tools==>options==>Calculations==>1904 date system

This will allow negative times.

But beware dates are offset by 4 years.

"Lerxst" wrote:

> I am trying to get a function that will add time in the format of
> hours:minutes. Each cell is the difference between actual time and forecast
> time. Therefore sometimes I have a negative value. I cannot add the values if
> I have a negative time cell.
>
> example:
> C1 is the difference between A1 and B1, lets say it equals :40 (40 minutes)
> C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
> C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
> under forecast)
> The totals for column C should equal 1:10 (1 hour 10 minutes) but it doesn't
> allow a negative time value.
>
> Any ideas how I can make this work and how I should format the cells????
>
>

If you want to use time format you can either change to 1904 system under
tools>options>calculation
however it will change dates in your system by adding 1462 days to any date
or you could use something like

=IF(A1>B1,ABS(B1-A1),B1-A1)

copy down

then to sum use

=SUMPRODUCT(--(B1:B30>A1:A30),C1:C30)-SUMPRODUCT(--(A1:A30>B1:B30),C1:C30)

will fail if the total is negative

you can also convert the time values to decimals and use them then convert
back to time but that won't help you if the total is negative


--

Regards,

Peo Sjoblom




"Lerxst" <[email protected]> wrote in message
news:[email protected]...
>I am trying to get a function that will add time in the format of
> hours:minutes. Each cell is the difference between actual time and
> forecast
> time. Therefore sometimes I have a negative value. I cannot add the values
> if
> I have a negative time cell.
>
> example:
> C1 is the difference between A1 and B1, lets say it equals :40 (40
> minutes)
> C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
> C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
> under forecast)
> The totals for column C should equal 1:10 (1 hour 10 minutes) but it
> doesn't
> allow a negative time value.
>
> Any ideas how I can make this work and how I should format the cells????
>
>

Since the actual date is a non-issue, this works great.

Thanks

"Toppers" wrote:

>
> tools==>options==>Calculations==>1904 date system
>
> This will allow negative times.
>
> But beware dates are offset by 4 years.
>
> "Lerxst" wrote:
>
> > I am trying to get a function that will add time in the format of
> > hours:minutes. Each cell is the difference between actual time and forecast
> > time. Therefore sometimes I have a negative value. I cannot add the values if
> > I have a negative time cell.
> >
> > example:
> > C1 is the difference between A1 and B1, lets say it equals :40 (40 minutes)
> > C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
> > C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
> > under forecast)
> > The totals for column C should equal 1:10 (1 hour 10 minutes) but it doesn't
> > allow a negative time value.
> >
> > Any ideas how I can make this work and how I should format the cells????
> >
> >

Got it working.

Thanks

"Peo Sjoblom" wrote:

> If you want to use time format you can either change to 1904 system under
> tools>options>calculation
> however it will change dates in your system by adding 1462 days to any date
> or you could use something like
>
> =IF(A1>B1,ABS(B1-A1),B1-A1)
>
> copy down
>
> then to sum use
>
> =SUMPRODUCT(--(B1:B30>A1:A30),C1:C30)-SUMPRODUCT(--(A1:A30>B1:B30),C1:C30)
>
> will fail if the total is negative
>
> you can also convert the time values to decimals and use them then convert
> back to time but that won't help you if the total is negative
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
>
> "Lerxst" <[email protected]> wrote in message
> news:[email protected]...
> >I am trying to get a function that will add time in the format of
> > hours:minutes. Each cell is the difference between actual time and
> > forecast
> > time. Therefore sometimes I have a negative value. I cannot add the values
> > if
> > I have a negative time cell.
> >
> > example:
> > C1 is the difference between A1 and B1, lets say it equals :40 (40
> > minutes)
> > C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
> > C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
> > under forecast)
> > The totals for column C should equal 1:10 (1 hour 10 minutes) but it
> > doesn't
> > allow a negative time value.
> >
> > Any ideas how I can make this work and how I should format the cells????
> >
> >
>
>
>