Formulas to Add shift penalty rates to specific times & days

[sman1]

Hey everybody im trying to create a complete spreadsheet that calculates all my penalty rates and need a little help with some more advanced formulas so that it calculates it automatically , listed below are my penalty rates which im hoping somebody can edit the attatched spreadsheet and help me out

Saturday - Time & a half
Sunday Double Time
Monday - Friday = Start before 07:00am 15% extra loading and finish after 19:00 is 20% loading also on monday friday anything over 8 hours is auto double time

Your help would be greatly appreciated....

[FDibbins]

Hi and welcome to the forum

We would love to help you with your question, but 1st, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

[AndyLitch]

Try this

Attachment 224814

[sman1]

Thank you so much its perfect just one more thing, i would like to change the currency symbol to a $ instead of the euro symbol please...

[sman1]

No dramas i got it sorted thanks heaps once again for your help

[AndyLitch]

Anytime bud..

[Kevin UK]

Hi sman1

Added a few more columns to your time sheet table.

[sman1]

Hey thanks for that just one more thing i needed the dates formatted as set in the file attached but once i changed the date formatting the hours etc stopped calculating in all cells.

[sman1]

If you could pelase help me with one more thing if possible, the loading of the 15% & 20% is for the entire shift not just the hour or so before or after 7am & 7pm

Your help is much appreciated and is invaluable thank you once again

[Kevin UK]

Hi sman1

See the attached file, column A as dates.

[sman1]

Thanks Kevin

Any ideas on what i can do with the last in regards to the last post ?

"If you could pelase help me with one more thing if possible, the loading of the 15% & 20% is for the entire shift not just the hour or so before or after 7am & 7pm

Your help is much appreciated and is invaluable thank you once again"

[Kevin UK]

Hi sman1


So if you start work at say 06:00 & finish at 14:00 = 8 hrs. Then you are paid 8 hrs @ 19.82 + 15% = 182.34, is that what you mean!
Also, is there any reason for the value "19.8229166666666" in B3 or could you just not round that to 19.82!

[sman1]

Yeah that's exactly right, only after 8 hours on a weekday do the hours start becoming double so if I worked 9 the last hour would be double the remaining 8 would be + 15% if started before 7am or 20% if finishing after 19:00etc... No reason for the extra digits in B3 I just entered anything giving it a number to work with rounding will be fine.

[Kevin UK]

Hi sman1

See the attached. What happens if you start work at 06:00 & finish at 20:00!

[Geoff0001]

Hi Kevin UK,

I know this post is very old but it is very similar to what i am trying to achieve, is there any chance you could share the file again as the files that are attached flag with an error. I know its a long shot and i appreciate any help.

[FDibbins]

Geoff, I just downloaded the file from post 14 with no problems?

[Geoff0001]

Hi Ford

Not sure what happened last night, but i retried this morning and it has worked. Thank you for reaching out to me.

Regards Geoff

[FDibbins]

Thanks for the update