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]

But if I don't work 10hrs plus 30 mins break, and only only worked 8hrs 2 1/2hrs would come off my overtime as I never worked 40hrs that week. If that makes sense

 

[cuddlezuk]

Or have a box below total overtime saying finished early less time, then below total overtime

 

[Toadstool]

So are you saying that overtime calculation is wrong because overtime should only come into effect after 40 hours have been worked that week?
If so then what days of the week define the overtime period?

 

[cuddlezuk]

I'm a truck driver, every day is different. Monthly pay, so if i work 2hrs overtime and finish 2hrs early next day, total overtime is 0

 

[cuddlezuk]

I see it adds up, I was checking the formula for the overtime, but working 2hrs less standard, comes off at the totals, and not listed as - overtime hrs

 

[Toadstool]

So Overtime is only calculated over the entire month? e.g. If you've worked 5 hours overtime the first week but only done 45 hours the last week then you get zero overtime? If overtime is on a monthly basis or weekly will change the calculation.

I'm confused if you have a standard 10 hour day or 40 hour a week? Could it be that you work a 4 day week so expect 4 ten hour days to get 40 hours?

The sheet assumes that an entry in the Start Time indicates it's a workday. If your employer treats a sick or vacation day as a workday for the purposes of overtime calculation then it becomes more complex.

There's no financial information here so if you could post the XL2BB for an actual complete month, such as July, and state the number of hours your employer paid you for overtime 20 July to 19 August then I could reverse engineer their calculation.

 

[cuddlezuk]

I get paid for 40hrs per week, but they take 30min break off you, so to get 40hrs, you have to work 30mins longer per day, I work 4 days.

So if I done 2hrs overtime mon, tue and wed then finished 2hrs early on thursday, that's just 4hrs overtime in that week, but paid monthly,

 

[cuddlezuk]

So the calculation for total overtime is wrong, as everyday needs to be 10 1/2hrs to get that overtime total, if finished 2hrs early one day, the overtime is 2hrs less in the total

 

[Toadstool]

So it sounds like Overtime is calculated weekly. Most weeks are defined as Sunday to Saturday so I can do a calculation for each week, but what happens at start/end of month?
e.g. 20 August is a Thursday so do I need to know the hours worked for August 16 to 19 to calculate?

 

[cuddlezuk]

Overtime is from 20th upto 19th, my working week is mon to thur.
They work out monthly pay by multiplying weekly salary by 52, then divide by 12, the overtime they have to add extra which deadline is 19th to go onto that months pay, plus if I am late they take the hourly rate off.