An example in German Excel edition:
Noun "Regelarbeitszeit" translates to "Default Worktime", "Arbeitszeit" translates to "Hours worked", "Überstunden" translates to "Overtime", ...
Hi all,
I have one challenge for you. Can some one help me I need a xls time sheet that calculates normal and overtime hours as I have difficulties creating one I have only basic excel knowledge.
I did try Google but nothing to what I need.
1.Mon-Fri: normal time 8:45 after next 3 hours 1.5x OT and the rest 2x OT less meal brake 30 min, pay rate $10 normal a hour.
2.Sat: first 3 hours 1.5x OT and the rest 2x OT.
3.Sun: all day 2x OT.
4.Option selection on any day for public holiday double time and half rate.
Thanks.
Last edited by roma; 17-08-14 at 12:26 PM.
Look Here -> |
An example in German Excel edition:
Noun "Regelarbeitszeit" translates to "Default Worktime", "Arbeitszeit" translates to "Hours worked", "Überstunden" translates to "Overtime", ...
Thanks jwoegerbauer but no much of help to me german and no over time.
is your friend!
Hi lsemmens thanks I did try but nothing to what I need please check I have edited my post.
Thanks.
Sounds like some basic formulae are all that's needed
psuedo code here as I don't have time to develop the entire thing
if pub holiday goto SUN else
if(days = mon - Fri) then
if(hours <= 8) and (time>0800 and time<1600) then normal
if(hours >8 and hours <11) then OT = 1.5 * normal
if(hours > 11) then OT = 2*normal
elseif(days = sat) then
if hours < 3 then OT = 1.5 * normal
if hours >3 then OT = 2* normal
elseif(days=Sun) then
goto end
:SUN
OT = 2*normal
goto END
:END
return with values calc
feel free to try my roster
its not formulas but you can put in what hours\prices you want
https://www.facebook.com/philquad68
Hey Roma, make up the basic spreadsheet - just how you want it to look. Put notes if you want a drop down list etc.. Just so I know what it's supposed to look like. Email it to draught4me@gmail.com and I'll add the formulas for you.
Cheers.
When I was a kid, I used to have an imaginary friend. I thought he went everywhere with me. I could talk to him and he could hear me, and he could grant me wishes and stuff too. But then I grew up, and stopped going to church.
Ok, try this. It's a bit dodgy. I started doing the correct way, but the nested 'ifs' got pretty long, then I forgot about the public holiday thing, and I got all confused
So I cheated and used vlookup. Downside is you are limited to the times I put in (there are quite a few).
It should work though. Just needs a bit of tidying up.
Cheers.
When I was a kid, I used to have an imaginary friend. I thought he went everywhere with me. I could talk to him and he could hear me, and he could grant me wishes and stuff too. But then I grew up, and stopped going to church.
Silly question but why do you need it? Is it a homework assignment? As, although involved, the formulae are pretty basic.
Hi all,
This is the closest to what I need just I don't know how to mod to my needs.
Please check the xlsx file.
Last edited by roma; 19-08-14 at 01:08 AM.
its not right anyway
the 1.5x doesnt work
only the 2x
https://www.facebook.com/philquad68
OK, I've changed the first day for you (Monday) to do what you want. Obviously you just need to copy that for the next 4 days. It shouldn't be too hard to work out Saturday and Sunday.
See how you go.
Cheers.
Oh, should mention that most of the stuff on the right of the page no longer works. Just the 'after 8:45 hours" and the 3 hours bit next to it (ref for how much overtime @ 1.5 ).
Cheers.
Last edited by Mods; 19-08-14 at 02:23 PM.
When I was a kid, I used to have an imaginary friend. I thought he went everywhere with me. I could talk to him and he could hear me, and he could grant me wishes and stuff too. But then I grew up, and stopped going to church.
roma (21-08-14)
Thanks Mods I have modified the file please check the copy and if can be improved. Only problem I have is when I trying to copy and paste to create more copy's of time cards/weeks the formulas are mixed as I'm trying to keep the master data on the top.
Please check if any one can fix this or improve it.
OK, You did a good job moving everything around - that can be quite time consuming, especially when it's not your own spreadsheet and you have to keep checking track of all the cells that are being referenced.
Attached you'll find a slightly modified version. The whole week should be working now. There's probably an easier way, but when you go to copy the spreadsheet you have two options. Do what you've done, and then you need to modify the absolute referenced (like everything in row 3 - as excel will change p3 to p62 (or something) - you need to change this back manually). Or the other option is you can just copy the whole sheet to a new tab (right click tab, and check the copy box) and you will have a fully working spreadsheet on a different tab - nothing to change.
Apart from that it seems to be working !
Cheers.
When I was a kid, I used to have an imaginary friend. I thought he went everywhere with me. I could talk to him and he could hear me, and he could grant me wishes and stuff too. But then I grew up, and stopped going to church.
What is the easiest way to copy and paste, as when I do the absolute reference changes on the copy'd data.
I'ts to involved to change every single copy'd formula as I'm trying to do for all year.
Please check the file.
Thanks.
Last edited by roma; 26-08-14 at 11:56 PM.
Can you just have each month as a separate tab at the bottom of the page ? That way, you just set up the first page (with only 4 weeks) and copy it to each tab. Then you can stick the little calendar thing next to it.
Cheers.
When I was a kid, I used to have an imaginary friend. I thought he went everywhere with me. I could talk to him and he could hear me, and he could grant me wishes and stuff too. But then I grew up, and stopped going to church.
Bookmarks