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