Time +/- calculation

[Fester675]

I am developing a flexi-working sheet for employees, and have run into a problem...

For any time input cells, I have used [h]:mm as the cell format. However I need to generate a running balance (either a flexi credit, or a flexi debit).
The sheet has 3 sections -

1. Day; Date; Morning time in/out; Midday time in/out; Afternoon time in/out - works fine
2. Worked (sum of actual hrs/min worked); Credit (any additional time credited); Total (total of worked + Credit) - works fine
3. Standard working day (7:24); Debit (amount less than standard); Credit (amount more than standard); Running Balance - this is where it goes wrong - the first cell (Monday) should take the balance carried forward from a particular cell and either -/+ this figure dependant on whether the Debit or Credit cell is completed. If Running Balance is a (-) figure then it should populate in red font and if (+) figure then it should populate in green.

The main issue is I can't do a formula to +/- on the running balance, dependant if there is a credit or debit overall - or at least, not one that will return my answer in the same [h]:mm format.

Any help is greatly appreciated!

 

[FormR]

<o7,"",k7-o7)))

How can I format the box for 'balance brought forward' if the value is a negative (debit) balance of hours/mins?

Do you mean you to format it red when it's negative? Did you try..

<o7,"",k7-o7)))
Once you get your times displayed the way you want, you can try a custom number format of:

[hh]:mm;[Red][hh]:mm

</o7,"",k7-o7)))
</o7,"",k7-o7)))

 

[Fester675]

It won't let me enter a negative time in the 'balance brought forward' cell e.g. -2:00

 

[FormR]

It won't let me enter a negative time in the 'balance brought forward' cell e.g. -2:00

I thought you had resolved that by using the 1904 date system

 

[Fester675]

It seems that the 1904 system works fine for using a formula to generate a cell to be completed, but the balance brought forward is a manual entry and it won't take a negative "-2:00" is returned as an error. If I the do '-2:00 then the running balance returns ####

Confused.

 

[FormR]

It seems that the 1904 system works fine for using a formula to generate a cell

Maybe use a formula then to enter your "manual" time:

i.e.

=-TIME(2,0,0)

 

[Fester675]

Really appreciate all your help on this!

I managed to get round it by entering -"2:00" and all calculations appear to work fine. Thank you again!!

 

[FormR]

I managed to get round it by entering -"2:00"

Cool, thanks for sharing