Summing negative time
 

Hi All,

I am having problems adding time values hwen a negative time is
involved. I have read all the posts on this and have chosen the 1904
date option, but no success.
I determine the difference between times and then subtract a standard
day from this amount to achieve a result (positive or negative). Then
I want to sum the results. Works fine as long as I don't have any
negative results. If I do have negative then the answer is always 0.


Example:


Morning.............Afternoon..............Balance
In.......Out........In........Out..........Total.. Flex...Balance
8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20
9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00
8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00
8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00


I have tried the following formulae:
= L3 + M4
= MOD ((L3+M4),1)
= SUM (L3:L4)

I am not the most proficient Excel user - if anyone has ideas I would
be most grateful.

Deb :)


--
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php...o&userid=25669
View this thread: http://www.excelforum.com/showthread...hreadid=390851

On Thu, 28 Jul 2005 01:41:04 -0500, Bumblebee
wrote:


Hi All,

I am having problems adding time values hwen a negative time is
involved. I have read all the posts on this and have chosen the 1904
date option, but no success.
I determine the difference between times and then subtract a standard
day from this amount to achieve a result (positive or negative). Then
I want to sum the results. Works fine as long as I don't have any
negative results. If I do have negative then the answer is always 0.


Example:


Morning.............Afternoon..............Balanc e
In.......Out........In........Out..........Total. .Flex...Balance
8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20
9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00
8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00
8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00


I have tried the following formulae:
= L3 + M4
= MOD ((L3+M4),1)
= SUM (L3:L4)

I am not the most proficient Excel user - if anyone has ideas I would
be most grateful.

Deb :)

I suspect your problem has nothing to do with the presence of negative times.
SUM and other operations work on negative times regardless of the date system.
Using the 1904 date system allows negative times to be DISPLAYED (as other than
#####) but does not change the actual contents of the cell.

I set up your data in cells A3:G6, with the labels in Rows 1 and 2.

In the Total column (E) I used the formula:

E3: =B3-A3+D3-C3

In the Flex column (F) I used the formula:

F3: =E3-TIME(7,,)

In the Balance column (G) I used the formula:

G3: =F3
G4: =G3+F4

I then selected G4 and copy/dragged down the formula, resulting in:

G5: =G4+F5
G6: =G5+F6

This summed the Balance of Flex Times:

Morning Afternoon Balance
In Out In Out Total Flex Balance
8:15 AM 12:35 PM 1:00 PM 5:00 PM 8:20 1:20 1:20
9:00 AM 12:55 PM 1:00 PM 3:15 PM 6:10 -0:50 0:30
8:30 AM 12:15 PM 1:30 PM 3:00 PM 5:15 -1:45 -1:15
8:00 AM 11:00 AM 11:30 AM 5:00 PM 8:30 1:30 0:15



--ron

Much as I try to avoid date/time questions, I can understand why Excel
doesn't like negative time, it cannot exist except in a mathmaticians
head.

Try another column for your flex, flex-under and flex-over and then you
should have no troubles.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=390851

Deb
Time format can't show negative values. One way is to convert the flexy hrs
and balance to decimal numbers

Total Hrs in f4 =C4-B4+E4-D4
Flex Hrs F4 =IF(F4<0.2917,(0.2917-F4)*24*-1,(F4-0.2917)*24)
Balance in G4 =SUM($G$4:G4)

and formulas copied down

Peter

"Bumblebee" wrote:


Hi All,

I am having problems adding time values hwen a negative time is
involved. I have read all the posts on this and have chosen the 1904
date option, but no success.
I determine the difference between times and then subtract a standard
day from this amount to achieve a result (positive or negative). Then
I want to sum the results. Works fine as long as I don't have any
negative results. If I do have negative then the answer is always 0.


Example:


Morning.............Afternoon..............Balance
In.......Out........In........Out..........Total.. Flex...Balance
8:15 AM..12:35 PM...1:00 PM...5:00:00 PM...8:20...1:20...1:20
9:00 AM..12:55 PM...1:00 PM...3:15:00 PM...6:10..-0:50...0:00
8:30 AM..12:15 PM...1:30 PM...3:00 PM......5:15..-1:45...0:00
8:00 AM..11:00 AM...11:30 AM..5:00 PM......8:30...1:30...0:00


I have tried the following formulae:
= L3 + M4
= MOD ((L3+M4),1)
= SUM (L3:L4)

I am not the most proficient Excel user - if anyone has ideas I would
be most grateful.

Deb :)


--
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php...o&userid=25669
View this thread: http://www.excelforum.com/showthread...hreadid=390851

On Thu, 28 Jul 2005 04:29:03 -0700, "PeterAtherton"
wrote:

Time format can't show negative values.

It can if one uses the 1904 Date system; which the OP wrote she was doing.


--ron

Many thanks for all your help - Ron's solution worked a treat :)


--
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php...o&userid=25669
View this thread: http://www.excelforum.com/showthread...hreadid=390851

I've just done some testing and discovered that if the Balance column
totals to more than 24 (either -24 or +24) then the balance starts
again from 0 in the next column. I assume this is to do with their
only being 24 hours in a day.

Total...Flex...Balance
8:20....1:20....1:20
6:10...-0:50....0:30
5:15...-1:45...-1:15
8:30....1:30....0:15
13:00...6:00....6:15
13:00...6:00...12:15
13:00...6:00...18:15***
13:00...6:00....0:15*** (should be 24:15)
13:00...6:00....6:15*** (should be 30:15)

I would like to hope that none of my staff will clock up more that 24
hours flex, but I can't guarantee it.

Any ideas how to fix the formula?


--
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php...o&userid=25669
View this thread: http://www.excelforum.com/showthread...hreadid=390851

On Thu, 28 Jul 2005 17:51:55 -0500, Bumblebee
wrote:


I've just done some testing and discovered that if the Balance column
totals to more than 24 (either -24 or +24) then the balance starts
again from 0 in the next column. I assume this is to do with their
only being 24 hours in a day.

Total...Flex...Balance
8:20....1:20....1:20
6:10...-0:50....0:30
5:15...-1:45...-1:15
8:30....1:30....0:15
13:00...6:00....6:15
13:00...6:00...12:15
13:00...6:00...18:15***
13:00...6:00....0:15*** (should be 24:15)
13:00...6:00....6:15*** (should be 30:15)

I would like to hope that none of my staff will clock up more that 24
hours flex, but I can't guarantee it.

Any ideas how to fix the formula?

Format the cell with the formula as:

[h]:mm

The brackets around the 'h' parameter prevent it from "rolling over" every 24
hours.


--ron

Cancel that request - I found out I have to change the format of the
coloumn to put square brackets around the h - [h]:mm and this has fixed
it.

:) :) :)


--
Bumblebee
------------------------------------------------------------------------
Bumblebee's Profile: http://www.excelforum.com/member.php...o&userid=25669
View this thread: http://www.excelforum.com/showthread...hreadid=390851