An award wage timesheet calculator.

Hi Everyone,

I am trying to create a timesheet calculator that will seperate the hours into different categories.

The award wage my staff are paid varies from what times they work, ie:
7am - 7pm
7pm - 12am
12am - 7am

I want to be able to just enter their start hours/break start/break finish/shift end times, and categorize automatically.

Just to confuse things even more, Saturday and Sunday have different rates aswell :/

Can this be done?

Thanks in advance!

Please feel free to ask any questions if I have not been specific enough.

Yes, I think you can do this! It would be easier if your time entries were military. Is that an option? Also will you be entering in the start time, break start, break finish, and shift end data into their own individual cells? Will the date be included in the cell with the time? Can you send a sample of your sheet? Can additional hidden cells be used to do calculations? Sorry to ask so many questions, but this one has a few ways to go.

Hey Motu,

thanks for the prompt reply.

Military times are perfect.

Yeah, the times will be entered into their own cells.

I'm going to use the one sheet for multiple people, and weekly. not too sure how i can go about this yet though.

Hidden cells are fine.

I have attached the manual one I'm using now as a reference and a rough of what it should kinda look like.

Thanks again mate

Okay here is what I have so far. There is a lot going on here. I haven't done anything with Saturday or Sunday, but you may be able to figure it out by looking at what I did. Please let me know if you need further help! I can do more later, but I need to take a break for while.Attachment 268578

You're an absolute genius mate. I just got home from a big day at work, and need a rest myself. I will play around with this tomorrow and let you know how I go. I really really appreciate the time and effort you have put into this. I will talk to my directors tomorrow, and if we can get this across the company, I'm sure they will be happy to give you something in return. Will let you know tommorow, again, THANK YOU THANK YOU! :D Enjoy your well earned rest!

Thank you for the kind words. Don't take everything on the sheet as perfect yet. It may still have some glitches, but it should get you in the right direction.

Thanks Fotis1991,
It's just amazing how something someone does out of their kindness can make big differences to people.

I appreciate the advice, and have marked this as solved.

Although, I was playing around a little, and noticed a slight (hopefully) mistake.

Timesheet mistake.png


When starting at 00:00, and finishing at 04:00 with a 1/2 hour break in between, it adds the hours correctly, but puts the times in the opposite categories.

I was also thinking, to get around the multiple names and multiple days issue, I could make a sheet for every day of the week, and then I could have a weekly section on the end of the sheets that would just pull totals out and make it easier for my accountant to see.

Thanks again Motu, no rush on this.

(I need to go through some back pays from the 1st of July 2013, entering multiple timesheets and split the hours, this would make my job 1000x easier for the backpays, and in the long run. plus some browny points on the side *fingers crossed*) :D

I am going to redo this sheet with a better format I should have it back to you within 24 hours. I hope that is okay.

That's fine!

Thanks again for all your work.

okay sorry for the delay. I think this gets it all. It took a lot more work than I expected.
Good luck with everything. Let me know if you need anything else on this.

Hey Motu,

I can see the extent of work you have put into this, it's incredible!

Unfortunately though, I'm pretty sure I'm using it correctly, but there are still some glitches.

Friday night shifts don't carry onto Saturday, and when using Saturday or Sunday as date, nothing is placed in the columns. :/

I really do appreciate the work you're putting in, I just wish I knew half of what you know so I could try repair or help out somehow.

Thanks again,
SoNiKBooM

hi try this

Wages Template2.xlsx

WOW.

This is very good!
The formatting is exceptional, it's exactly what I'm after in that sense and it works very well.

Not sure if I'm asking too much or not, but is it possible to show a Fri night shift, say 11PM Friday night to 1AM Sat morning, split up over the 2 days?
Rather than showing just worked the friday? The same for sat/sun shifts as well. sun/mon is not an issue.
I could enter it manually, but I'd really like this to be fully automatic so other staff can use it too.

Also, i dont understand Rule 4. "I have set the Nightshift break for 30mins and if anyone works past 2am it will be automatically taken off." ?
-What is this for?


I love the layout and format of this workbook, also how well the cells are locked up.

IF it could just split the weekend rates now, I'd be ever so grateful!!

Thank you Toonies!

Hi quick question

which sheet are you referring to when you say you want, Fri night shift, say 11PM Friday night to 1AM Sat morning, split up over the 2 days?

To clear up Rule 4, refers to your previous statement from reply No 8.

"When starting at 00:00, and finishing at 04:00 with a 1/2 hour break in between"

Just on the 'Wages Template' sheet would be fine.

Sorry, that statement in reply #8 is an example. I hope it's not too hard to remove. (Although, I don't think it was working anyhow)

Thanks again mate

Hi I will have a look, I will get back to you soon

Hi Toonies,

Just seeing how you're going with the adjustment mate?

I hope it's not being too much of a headache.

I appreciate your time and effort, Thank You.

hope this is what you are after


Wages Template3.xls

Well, that is spot on!

Thank you so much Toonies. I can't thank you enough for your efforts.

I have played around with this for a few days now and it works brilliantly!

One issue I did notice is anything from 00:00 cuts half an hour off the total.

Eg:

pWHZDRX.png

Apart from that, this is exactly what I need.

I have added an Hours sheet, and made some other mods as well.

Wages Template NEW w Hours.xls

Once again, I really appreciate your work.

Thank you!

I have had a look and all you need to do is put the following break code "N 30" in Cell M30 on the Breaks table - Wages Template sheet, as it blank at present. Once you have done that it should work fine.

Hi I have amended your Hour Template sheet so that it works.

Wages Template4.xls