complex timesheet

Looking this over...
Our slightly different dialects are making it somewhat difficult for me to understand exactly what you're meaning is for some of these, but I'll see if I can come up with something and throw it your way for some feedback.

mew!

Lets start with normal hours and delve further as we make progress:

Expected to work 80 hours per fortnight at $26.17/Hr
Only paid 95% of these hours worked – remainder accumulates towards leave. (ie only paid for 76 hours (95%) of 80 hours)

1) All time worked in excess of 80 hours per fortnight (becomes rostered overtime) - common

(2) All time worked in excess of ten hours in any one shift (unrostered overtime) –uncommon

So - Daily normal hours:
- maximum possible is 10/day. Any more than that will be counted as unrostered OT
- maximum possible is 80 per 2 week period? (I don't know what a fortnight is). Any more than that will be counted as rostered overtime.
- of these normal hours, an employee is only paid for 95% of them, and the remaining 5% is allocated as leave pay

Does that correctly describe treatment of normal hours and normal pay?

Couple quick questions -
- penalty hours? Is that a deduction off of normal pay IE :

Hours worked between 6.00 p.m. and midnight, from Monday to Friday - 12.5% ($3.27) extra per hour.

they make 12.5% LESS during that period? Or more... The word "Penalty" indicates it's a deduction, but the way you have it described it sounds like a 12.5% extra pay for hours in that period.

Also I'm not sure what you mean by "Rostered" and "Unrostered" time.

I realize there's quite the time difference between us there, so I'm trying to make some headway on guesswork :P

See attached file. I believe I've entered a column for each of your 4 conditions concerning regular time. These now calculate based on the criteria you've listed.

Please take the time to run through a few scenarios in terms of hours worked vs expected outcome vs real outcome.

The columns/cells which i've added/changed are highly visible in SUPER-PUNCH-IN-THE-FACE-BRIGHT-YELLOW. That should make it easier for you to review those conditions which I've entered. NOTE: I'd recommend keeping these columns seperate, and hiding them after you're sure they do what you want them to, for easier revisions and checks in the future.

meow.

kk just let me know how we're looking so far. I didn't stop because I don't know how to do the rest, but it's time consuming, and if I haven't started properly, I'm just gonna have to do it all over again anyways.

eesh, k.
I'm not gonna have time to do this 'till tomorrow evening at the earliest. Times crossing over to the next day without any kind of date associated with them is going to complicate these formulas a great deal, since they'll all need allowance not only to calculate the hours, for that day, but also for the next, including your four conditions.

It's definitely possible, it's just gonna take me more time than I have tonight.

I am gonna try to bring this to the attention of some of the others on here. I've done some work with timesheets, but not a ton, and one of them may be able to get you set up quicker and more efficiently than myself, but I'll work with ya if no one else picks it up.

mew!

I would need a you to manually enter 4 or 5 full sets of hourly calculations in the sheet showing all the hurdles that have to be overcome. No way to just talk through this, need to make one work, then add another and tweak to make it work, til you get them all.

For instance, what is i,ii,iii and iv? 100%, 112.5%, 125% and 150% pay? That should be evident on the sheet, yes?

FIRST CONTRIBUTION:

In cell J6, use this formula, copy down. Format the cell as GENERAL. much simpler way to add time past midnight.

=(E6-D6+(D6>E6))*24

Cell O16 slightly simpler formula :-

=MOD((ROUND(L16*48,0)-ROUND(K16*48,0))/2,24)

will calculate if there are 2 hours overtime on this value

=2*(O16>=2)

Subtract the second from the first to get the remaining overtime!

PS JB! same formula above, seems in his j6 is rounding to nearest 30 minutes hence the lenght and using mod to calculate midnight!

wouldnt it be better to round the answer to the nearest rather than the times to be subtracted?

Will take a look at the rest for you, BTW there are similar problems posted on here!

Well Firstly :-

HOUR(E11)-HOUR(D11)

change this to

mod(e11-d11,1)*24

or if you want to the nearest 30

round(mod(e11-d11,1)*48,0)/2

or Whole Hours

round(mod(e11-d11,1)*24,0)

(can substitute roundup/rounddown if you dont want the follow the 0.5 rule!

24:00 = 0:00 as there is no such time as 24:00

excel should store 24:00 as 1.0 so

=hour(a1)+24*day(a1)

should give you what you want