Forum Discussion
Calculating +/- hours.
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...
Unfortunately screenshot disappeared.
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)*24applying General or Number format.
Total for year you may convert back to text if that's more suitable.
- 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...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.