Calculating +/- hours.

[Graeme3257]

Hello,

My knowledge of Excel is very basic but have a problem that I need to solve, and hope Excel is the solution.

At my work I work a shift rota. At the end of the month I complete my timesheet, and it compares my contracted hours against the actual hours I work (planned hours) and then the software used calculates whether I am "up" or "down" on hours worked. Herein lies the problem - their figures are incorrect and there is a dispute, going back to the beginning of 2015.

I am trying to prove that their figures are incorrect, and I'm 99% sure they are.

So what am I trying to achieve?

I have created a worksheet with a number of tabs, one for each year, and each tab has 12 rows, one for each month. Column B has my planned hours in hh:mm format, and Column C has contracted hours, again in hh:mm format.

I have created a formula for Column D which gives a total for each month expressed as hh:mm and these are either a positive or negative value, but although it is displaying the correct data, apparently it is actually a TEXT value.

All I am trying to do is have a "result" box which calculates the total for each year, whether is is a plus or negative value but I'm really struggling and unsure if this is possible?

Attached is a screenshot of what I currently have, although the columns are different letters for the "actual hours" worked.

Any suggestions?

Please note, the final column on the right is where someone has tried to convert to decimal to make the calculation.



 

[#2]

@Graeme3257 

Unfortunately screenshot disappeared.

[#3]

 

@Sergei Baklan 

[#4]

@Graeme3257 

You may use 1904 date system, but that could be side effects. Another option is to use decimal values like in column F of your sample. However, correct values could be calculated as

=(B2 - C2)*24

applying General or Number format.

Total for year you may convert back to text if that's more suitable.

[#5]

Thanks for the response. In the absence of a quick and simple method, I have resorted to a further 4 columns - plus hours, plus minutes as decimal values, minus hours and minus minutes as decimal values. I have used SUM for each column and have then done the conversion from decimal to minutes.
It would be great if there was a way to do it automatically so I can share the blank worksheet with colleagues...

[#6]

@Graeme3257 

If texts are enough and you don't do any other calculations with the result, that could be

=IF(B2 < C2, "-", "") &
    TEXT(ABS(B2 - C2), "[hh]:mm")

in each row and

=IF(SUMPRODUCT(B2:B5 - C2:C5) < 0, "-", "") &
    TEXT(
        ABS(SUMPRODUCT(B2:B5 - C2:C5)),
        "[hh]:mm"
    )

for the summary for such sample

On modern Excel above could be done bit different way, but idea is the same.