Calculating Time Differences

[Brandy]

I am trying to calculate and subtract time formulas. I
have a time in and time out which gives me a 'net time'.
Then I also keep a carry forward of time--the net time
from each line adds to the time above for a carry over
time. If I need to subtract from the carry over time,
how do I set my cell so that it will know to subtract tht
particular cell when normally it adds the information in
that cell without going in and modifying my cell formula
each time?Example: Cell H6+G7=H7(and this carries forward
each line) the next line is Cell H7+G8=H8, etc. Then
along comes a negative time in G cell, how do I get it to
subtract that whithout changing the formula? If I put a -
in front of the figure or enclose it in parenthasis, it
doesn't work. I have to make that field H13-G12=H14 and
then change it back to a plus for the next row.

 

[Bob Phillips]

Brandy,

If one time goes negative, it will not format correctly, but it will still
sum okay.

So, for instance, if H6 comes out at -2 hours, it will show as #######, but
if G7 works out as 6, with your formula you will get 4 in H7. I am assuming
that all cells as formatted as time. This doesn't look good, but works, so
is this acceptable, or do you want it to look better as well, or even is not
the problem at all?

 

[Mark D]

One approach would be to modify your formulas slightly.

Assuming that your Time in is in column E, your time out in column F, your
Net Time in column G and your carry forward in column H.

Change the formula for net time from =F8-E8 to =IF(E8="amend",F8,F8-E8)
Change the forumla for carry forward from =H7+G8 to
=IF(E8="amend",H7-G8,H7+G8)

Now, if you enter the word amend in column E (time in) and the amount you
want to amend by in column F (time out) your total will automatically deduct
time instead of adding it.

Mark D

 

[brandy]

I didn't explain this sufficiently. Your responses are
logical, but don't work, so let me re-word this. The
columns are formatted for 'time' If I type a word
(amend) in Col E, the other columns ignore it, but the
intended result is what I am looking for. On row 8, If
col B is amend, then whatever 'time' I type in column G
should automatically go in Colum H, then Col I7 will
subtract H8 for the I8 result(carryover), otherwise I7
adds to H8 for a new I8. On row 9, col B is a time in,
Col G is a time out, Col H is the difference and Col I8
+H9 = new carryover--my 'amend' function will be used
only occaisionally.

 

[brandy]

Correction to column name given in previous email

-----Original Message-----
I didn't explain this sufficiently. Your responses are
logical, but don't work, so let me re-word this. The
columns are formatted for 'time' If I type a word
(amend) in Col B, the other columns ignore it, but the
intended result is what I am looking for. On row 8, If
col B is amend, then whatever 'time' I type in column G
should automatically go in Colum H, then Col I7 will
subtract H8 for the I8 result(carryover), otherwise I7
adds to H8 for a new I8. On row 9, col B is a time in,
Col G is a time out, Col H is the difference and Col I8
+H9 = new carryover--my 'amend' function will be used
only occaisionally. out
in column F, your it
.