Results 1 to 20 of 20

Thread: Exce timesheet help

  1. #1
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default Excel timesheet help

    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 ->
  • #2
    Banned

    Join Date
    Feb 2012
    Posts
    2,361
    Thanks
    166
    Thanked 1,205 Times in 607 Posts
    Rep Power
    0
    Reputation
    16611

    Default

    An example in German Excel edition:



    Noun "Regelarbeitszeit" translates to "Default Worktime", "Arbeitszeit" translates to "Hours worked", "Überstunden" translates to "Overtime", ...

  • #3
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    Thanks jwoegerbauer but no much of help to me german and no over time.

  • #4
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,868
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    is your friend!

  • #5
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    Hi lsemmens thanks I did try but nothing to what I need please check I have edited my post.

    Thanks.

  • #6
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,868
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    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

  • #7
    Senior Member
    Philquad's Avatar
    Join Date
    Jan 2008
    Location
    nelson bay
    Age
    55
    Posts
    3,872
    Thanks
    192
    Thanked 1,305 Times in 783 Posts
    Rep Power
    665
    Reputation
    16938

    Default

    feel free to try my roster

    its not formulas but you can put in what hours\prices you want
    https://www.facebook.com/philquad68

  • #8
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    lsemmens I know is basic but for me is a bit difficult to put this together
    Quote Originally Posted by lsemmens View Post
    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

  • #9
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    Thanks Philquad it has no formulas you have to manually in put in the hours normal and O.T
    Quote Originally Posted by Philquad View Post
    feel free to try my roster

    its not formulas but you can put in what hours\prices you want

  • #10
    Banned

    Join Date
    Feb 2012
    Posts
    2,361
    Thanks
    166
    Thanked 1,205 Times in 607 Posts
    Rep Power
    0
    Reputation
    16611

    Default

    Quote Originally Posted by roma View Post
    Thanks Philquad it has no formulas you have to manually in put in the hours normal and O.T
    Can't be so difficult to insert the formulas.
    Last edited by jwoegerbauer; 17-08-14 at 11:47 PM.

  • #11
    Senior Member Mods's Avatar
    Join Date
    Jan 2008
    Age
    55
    Posts
    596
    Thanks
    260
    Thanked 261 Times in 132 Posts
    Rep Power
    263
    Reputation
    2305

    Default

    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.

  • #12
    Senior Member Mods's Avatar
    Join Date
    Jan 2008
    Age
    55
    Posts
    596
    Thanks
    260
    Thanked 261 Times in 132 Posts
    Rep Power
    263
    Reputation
    2305

    Default



    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.

  • #13
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,868
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    Silly question but why do you need it? Is it a homework assignment? As, although involved, the formulae are pretty basic.

  • #14
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default Weekly Time Card

    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.

  • #15
    Senior Member
    Philquad's Avatar
    Join Date
    Jan 2008
    Location
    nelson bay
    Age
    55
    Posts
    3,872
    Thanks
    192
    Thanked 1,305 Times in 783 Posts
    Rep Power
    665
    Reputation
    16938

    Default

    its not right anyway
    the 1.5x doesnt work
    only the 2x
    https://www.facebook.com/philquad68

  • #16
    Senior Member Mods's Avatar
    Join Date
    Jan 2008
    Age
    55
    Posts
    596
    Thanks
    260
    Thanked 261 Times in 132 Posts
    Rep Power
    263
    Reputation
    2305

    Default

    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.

  • The Following User Says Thank You to Mods For This Useful Post:

    roma (21-08-14)

  • #17
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    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.



    Quote Originally Posted by Mods View Post
    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.

  • #18
    Senior Member Mods's Avatar
    Join Date
    Jan 2008
    Age
    55
    Posts
    596
    Thanks
    260
    Thanked 261 Times in 132 Posts
    Rep Power
    263
    Reputation
    2305

    Default

    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.

  • #19
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    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.

  • #20
    Senior Member Mods's Avatar
    Join Date
    Jan 2008
    Age
    55
    Posts
    596
    Thanks
    260
    Thanked 261 Times in 132 Posts
    Rep Power
    263
    Reputation
    2305

    Default

    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

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •