EXCEL MONTHLY TIMESHEET NIGHTMARE

[cuddlezuk]

I've been trying to make a timesheet for my work, so my overtime gets paid to me.

Looking for a monthly timesheet starting 20th of every month to the 19th.

Monday to Thursday, but paid in 15 minute intervals, so if I clock in at 0520, I get paid from 0530, if I clock out at 1720, I get paid to 1715. as the round it back if before clock out early, and round it forward if I clock in early.

So a time sheet, with Day, Date, Start Time, 30 Min Break deduction, Finish Time, Showing my 10 hrs normal time with the break, anything over is overtime, so 10hrs 30mins normal, as they deduct the break,

At the bottom, total hours worked, plus total overtime at the bottom?

I can't get it myself, tried, tried and tried, without any success.
Can you get 24hr clock displayed, so start time 0520 and 1730 end time ?

Any chance you can throw one together, so I can tweak it if needed?

 

[cuddlezuk]

then days look ok, but dates are all 44063 44064, etc, plus times, break, everythink doesn't add up

 

[cuddlezuk]

all the data, even puting in times doesn't add up

 

[cuddlezuk]

all the data doesn't add up, plus dates don't get shower

 

[Toadstool]

Cuddlezuk,
Excel holds dates as number of days since 1 Jan 1900 and times as fractions of a day.
The sheet looks OK but you'll need to format the numbers.

Select all date fields and right-click, Format Cells, Date, the type you want to see.
Select all time fields and right-click, Format Cells, Time, the type you want to see.
Don't forget for the Totals at the bottom you'll need to select those fields, right-click, Format Cells, Custom and enter a Type of [h]:mm because those aren't time of day but duration.

 

[cuddlezuk]

That worked perfect, thanks!!!!! I would never thought of that..

 

[cuddlezuk]

Totals don't add up correctly

 

[Toadstool]

What do the totals show on my example data?
Don't forget to format Totals as described above.

 

[cuddlezuk]

Nothing like the right total

 

[cuddlezuk]

Changed the date, the bottom two days and dates not being displayed.

 

[Toadstool]

It looks like you've formatted the Totals as Custom hh:mm:ss
You must format the Totals (as previously described) to Custom [h]:mm