Summing negative time

[Bumblebee]

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

 

[Ron Rosenfeld]

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

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

 

[Bryan Hessey]

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.

 

[PeterAtherton]

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

 

[Ron Rosenfeld]

Time format can't show negative values.

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


--ron

 

[Bumblebee]

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

 

[Bumblebee]

I've just done some testing and discovered that if the Balance colum
totals to more than 24 (either -24 or +24) then the balance start
again from 0 in the next column. I assume this is to do with thei
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 2
hours flex, but I can't guarantee it.

Any ideas how to fix the formula

 

[Ron Rosenfeld]

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

 

[Bumblebee]

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

: