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]

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.

Hi, I think you're going to need to post some sample data - try to include what the expected results should be in relation to the sample data and if not obvious provide an explanation of how those expected results were arrived at.

 

[Fester675]

Flexi Time - 25'09'2017 - '20'10'2017 - User:

Balance brought forward:

0:00

Morning

Mid-session

Afternoon

Totals & Credits

Day Balance

Running Balance

Day

Date

AM

AM

Mid

Mid

PM

PM

Worked

Credit

Total

Std

Dr

Cr

Mon

25/09/2017

7:00

10:00

12:00

14:00

16:00

18:00

7:00

7:00

7:24

0:24

0.02

Tue

26/09/2017

8:00

12:30

13:00

17:30

9:00

9:00

7:24

1:36

Wed

27/09/2017

9:00

12:00

12:30

14:00

4:30

4:30

7:24

2:54

Thu

28/09/2017

9:00

12:00

12:30

18:00

8:30

8:30

7:24

1:06

Fri

29/09/2017

8:00

10:00

11:00

14:00

14:30

18:00

8:30

8:30

7:24

1:06

37:30

37:30

3:18

3:48

Total Hours

37:30

less Standard Hours

37:00

Weekly Balance

0:30

debit

<colgroup><col width="64" style="width: 48pt;"> <col width="42" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1479;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2247;" span="6"> <col width="6" style="width: 5pt; mso-width-source: userset; mso-width-alt: 227;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2247;" span="3"> <col width="6" style="width: 5pt; mso-width-source: userset; mso-width-alt: 227;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2247;" span="4"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[Fester675]

Flexi Time - 25'09'2017 - '20'10'2017 - User:

Balance brought forward:

0:00

Morning

Mid-session

Afternoon

Totals & Credits

Day Balance

Running Balance

Day

Date

AM

AM

Mid

Mid

PM

PM

Worked

Credit

Total

Std

Dr

Cr

Mon

25/09/2017

7:00

10:00

12:00

14:00

16:00

18:00

7:00

7:00

7:24

0:24

0.02

Tue

26/09/2017

8:00

12:30

13:00

17:30

9:00

9:00

7:24

1:36

Wed

27/09/2017

9:00

12:00

12:30

14:00

4:30

4:30

7:24

2:54

Thu

28/09/2017

9:00

12:00

12:30

18:00

8:30

8:30

7:24

1:06

Fri

29/09/2017

8:00

10:00

11:00

14:00

14:30

18:00

8:30

8:30

7:24

1:06

37:30

37:30

3:18

3:48

Total Hours

37:30

less Standard Hours

37:00

Weekly Balance

0:30

debit

<tbody>
</tbody>

 

[FormR]

and if not obvious provide an explanation of how those expected results were arrived at.

Hi, it's not entirely obvious, where are the expected results and how were they (manually) calculated?

 

[Fester675]

The problem is, I'm on a secure network and can't directly upload a file or image.

Ok - the problem is this...

Balance brought forward = 0:00 (this is the starting balance)

Day Balance - std remains static 7:24;

Dr col (K7=worked fig., O7=Std) =IF(K7=0,"",IF(K7=O7,"",IF(K7>O7,"",O7-K7)))

Cr col =IF(K7=0,"",IF(K7=O7,"",IF(K7<O7,"",K7-O7)))

Running balance (P7=Dr; O3=Running balance fig.; Q7=Cr) {=IF(P7="",O3+Q7,IF(Q7="",O3-P7))}

The running balance returns ##### and won't return [h]:mm, when this is corrected I also need to change font colour based on whether the return value is a debit (red) or a credit (green)

 

[FormR]

can't directly upload a file or image.

Hi, not been asking for either of those.

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

The running balance returns ##### and won't return [h]:mm, when this is corrected I also need to change font colour based on whether the return value is a debit (red) or a credit (green)

Seems the problem you are having is with negative times; maybe you can use the 1904 date system:

https://support.microsoft.com/en-hk...e-value-are-displayed-as-pound-signs-in-excel

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)))

 

[Fester675]

I will check that out now - thank you for your time and effort

 

[Fester675]

Worked perfectly!! Thank you so much!

 

[Fester675]

I've encountered another issue - sorry!

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