+ Reply to Thread
Results 1 to 13 of 13

Timesheet OT Vacation Sick, etc

  1. #1
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213

    Timesheet OT Vacation Sick, etc

    I have a timesheet I was able to create with the help of this forum. I have a little gliche that I was wondering if there is a solution for.
    If a person works overtime, and uses a benefit on the last day of the week, the overtime splits wrong.
    Currently, it will split the vacation or sick, whichever benefit is used, and put part of that into OT. (You will see what I mean by the example attached).

    Is there a way to make it so that if any benefit is used (such as Vacation, Sick, Hoiday or Funeral) that those hours will remain in the regular column, and the overtime gets allocated to the account codes used prior to the benefit?

    In the example attached, the VAC should be 8 hours regular, and the code above it, 6721 should be 32 hours regular time, 6 hours of OT.

    Since so many employees tend to use a benefit on a Friday, this happens everytime there is OT and we have to redo the sheet. I was hoping there was a better solution.

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    I sort of scrapped the original formulas in columns H and I and took a different approach - will this new spreadsheet work for you? This spreadsheet puts all "other" time first before regular hours.
    Attached Files Attached Files
    Ecce Potestas Casei
    Nathan Head

  3. #3
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    Side note - I think is is ironic that a "Funeral" day is abbreviated as "FUN". Last time I checked I didn't have much fun at any funerals .

  4. #4
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213
    HEY....that looks awesome! You must be a wizard!
    I will try it on some of the other complicated sheets to check all the options, but it apprears to work nicely on this one!
    Thank you!

    PS. FUN....you are right! It is a bit ironic that they used FUN for funeral....I commented on that when I started here as well.

  5. #5
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213
    I copied that formula to another person's timesheet. But once I do that, it does not put any of his time into overtime. I don't really understand the formula you added, so I don't know how I need to fix that. I attached the other one. Thanks again!!!!!
    Attached Files Attached Files

  6. #6
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    When I designed the original formula I only made it go down 25 rows. Your new spreadsheet has 27 rows of data.

    I modified the spreadsheet to go down to 100 rows. You may continue to increase as needed - just remember the formula is an array formula and must be entered with CTRL-SHIFT-ENTER if you change any of the items in the formula.

    Good luck!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213
    That makes sense! The only problem I have then, is that the formulas need to be in the blank spaces so it calculates as the employee fills in the form. It did that before with the "if this cell is blank, then blank, etc. But I can't seem to do it with this complicated formula. Wen I copy the formula down, I get this: 0:00 #VALUE! all the way down. The template is set now to go to about 4 pages since sometimes the techs do have timesheets that long for one week.

    I appreciate your help very much! I am trying to learn to do more with excel and I am taking as many classes as I can. But this helps so much!

  8. #8
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    Your wish is my command - new file enclosed.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213
    I ran into a bit of a snag that baffled me!
    Take a look at the example you uploaded. Change the first line end time to 12:30. It makes the OT formula error out causing the whole sheet to error.
    One of the employees called me because she was trying to use that time of 8 am to 12:30 pm. Not sure why it does that.
    Thanks again for your help....I wish I had your knowledge!

  10. #10
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    Change the formula in I5 to the following:

    =IF(G8="","",+ROUND(G8-H8,10))

    The reason you got an error is because Excel isn't always bright. With that particular value of 12:30 Excel was calculating the overtime as -0.000000000001 instead of zero. As such, since it was negative time, Excel displayed an error.

    The above formula will round the value to 10 significant digits....which is will down into 1/10 of a second accuracy (and you probably only need minute accuracy).

  11. #11
    Registered User
    Join Date
    12-20-2014
    Location
    reserve
    MS-Off Ver
    2013
    Posts
    4

    Re: Timesheet OT Vacation Sick, etc

    I am having the same type of problem adding in Holiday/Vacation Time. I need the holiday and vacation time to be regular time and not calculated with overtime.
    Basically holiday/vacation is not part of regular pay. if i have 20 hours of vacation then i would have to work an extra 20 hrs regular time before overtime kicks in. attached here is a copy of the spread sheet we use for our time cards. can anyone help me out with this??

    TimeCards test.xlsx

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Timesheet OT Vacation Sick, etc

    Hi iroussel, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    12-20-2014
    Location
    reserve
    MS-Off Ver
    2013
    Posts
    4

    Re: Timesheet OT Vacation Sick, etc

    how do i create my own thread? i have not found anyweres to start my own thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1