Spreadsheet or software to calculate hours worked?

C_Ronaldo

Conor wrote: »

I manage to work out my total hours, total breaks, total working time and total periods of availability as well as the amount I've driven in km on a daily basis and that's after a 10-15hr shift starting or finishing in the wee hours when the last thing you are is fully corpus mentus.

And I'm a lorry driver. So what you're saying is you're thicker than a lorry driver?
I only ask because if you can't work out something as simple as the hours you've worked and can't get it right without the use of a computer, you must be.

the OP wants it to be clear to her self and to her employer if they face any discrepencies

Conor_3

C_Ronaldo wrote: »

the OP wants it to be clear to her self and to her employer if they face any discrepencies

I complete a written timesheet every week in a similar vein to what the OP wants to do. My pay is correct every week so I assume they're intelligent enough to understand it.

devils_advocate

This is probably a bit clunky, but effective. Its a copy of the one i use, so once you get it set up, you can change colums etc to your needs.

format cols c to g to 2 decimal places

date in col b. assume this is row 3
cols c, d, e and f are headed in, out, in out representing times to start and finish before and after lunch.
in col g type =p3
cols h to k are blank (i use for notes to myself)
in col l type =((c3-int(c3))*1.667)+int(c3)
in col m type =((d3-int(d3))*1.667)+int(d3)
in col n type =((e3-int(e3))*1.667)+int(e3)
in col o type =((f3-int(f3))*1.667)+int(f3)
in col o type=(o3-n3)+(m3-l3)

copy row 3 as many times as needed - say 5 times for a five day week
then in the row after the last row, in col g, you can sum the amounts above it for the total hours worked in the week

Then copy the block again for the next week.

you can then enter times in cols c, d, e and f. for example, 8.3 is 8:30, 10.15 is quarter past 10. always use the 24 hour clock.

hope this helps!

bookduck

just be careful if you work carries over to the next day, or you work shifts, or on-call:

you might start at 23:00 and finish at 1:00 , so you have worked a negative 22 hours and owe the company money Yes, it is obvious but when you are quickly doing bulk inputs over a length of time it is easily overlooked

dazriel

Hi

I am looking for either a spreadsheet template which would work with Excel, or suggestions for timesheet -specific applications which would do this for me:

Calculate my hours worked per day and add or subtract them to/from my expected hours per day to see if I am over or under my normal hours, then keep a running total so I can easily see if I am up or down hours any week.
I have a spreadsheet in Excel which I use but the problem is when I work less than my normal hours, or have a minus amount of time, Excel cannot display it. Apparently no Microsoft applications can display time in a negative amount. I found a formula which will display the negative amount as a text field, but this can't be used then for further calculations, and my HR people want to be able to calculate hours by month etc.

Thanks for your help

Daz

II_D2_II

why can people just not use a calculator and their heads nowadays lool

buglawton

How about hide the time as a hidden column then the text display of it in the next column but not hidden? Use the hidden one for calcs.

dazriel

Thanks for that Bug

The real problem is that Excel simply can't handle a negative amount of time and won't even display it - it comes out as #########, so you can't do caculations based on it. At the moment the text field at least displays the amount of negative time correctly, so I just manually add or subtract this to the running total. Since I only run into negative amounts a few days a month, its not too bad, but I am sure there are simple timesheet or payroll calculators out there which would do this for me. Most important thing is I need to be able to save all the data and be able to show figures going back to Jan.

D

totalsolutions

dazriel, the ###### means excel cannot display the cell. Just make it wider, and it does calculate using the cell value, even if if cannot display it.

dazriel

The field displays ####### no matter how wide you make it - and when you click on it it says "negative dates or times are displayed as ############"