Forum Discussion
Graeme3257
Jan 25, 2023Copper Contributor
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 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.

Unfortunately screenshot disappeared.
- Graeme3257Copper Contributor
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.