Calculating time and negative time

[Josh UK]

Hi, I am trying to work out hours to carry over to the next period. They
could be negative hours. I have been having difficulty with the TOTAL and am
currently using the ABS formulae. I have changed the calculation formula to
1904 date system.

I have 2 questions:

Q1: What formula do I use to calculate B5?
Q2: If B5 is a negative when I link cell to next sheet will B3 calculate
negative number correctly?

B
Balance of hours brought forward from previous period 1
14:10:00
Total Hours worked in current Period 2 49:40:00
TOTAL formula: ABS(B1+B2) 3 63:50:00
Contracted Hours for Period (fortnight) 4 70:00:00
Balance of hours carried to next period 5
?

Would really appreciate some help - have been reading the discussion board
and trying different methods for hours!

 

[Jerry W. Lewis]

If =ABS(B1+B2) does what you want in B3, then it is not clear what you are
trying to accomplish.

Otherwise, I would assume that 70 hours per fortnight were contracted, and
if less were worked, then the carryover should be negative and would be taken
off the total in the next period. That interpretation would make the formulas
=B1+B2
in B3, and
=B3-B4
in B5.

Jerry

 

[JLatham]

You've taken the first step by changing to the 1904 date system (caution -
can create confusion if you use it with information from other workbooks
using the 1900 date system).

Format the column where your times are kept (B) as Time and choose the
option I see in my list as 37:30:55
Then just treat them normally be sure to enter at least the minutes part,
70:00 will give you 70 hours, but just 70 will show 1680 (70 days * 24 hours)

B
1 14:10:00
2 49:40:50
3 =SUM(B1:B2) or =B1+B2 displays 63:50:00
4 70:00:00
5 =B4-B3 (displays 6:10:00)

or another scenario:
B
1 14:10:00
2 65:40:50
3 =SUM(B1:B2) or =B1+B2 displays 79:50:00
4 70:00:00
5 =B4-B3 (displays -9:50:00)

 

[Josh UK]

Thanks - The problem was inconsistent formatiing from one shee to the next.