+ Reply to Thread
Results 1 to 24 of 24

Timesheet formulas that include leave entitlements

  1. #1
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Timesheet formulas that include leave entitlements

    Hi,

    I am having issues with a timesheet I use for work.
    An excel forum member has helped me previously with a solution that is perfect.
    Except, I would now like to add leave entitlements to the spreadsheet and have them automatically calculating like the rest of the sheet.

    On the sheet, when I select start and finish times all the relevant hours are calculated and wage (rate) is updated with corresponding start time.
    I would like to be able to select a leave entitlement from a drop down and then populate the cells relating to the hours received and wage owing.

    Is there a way when a 'word' is selected instead of a start time that the sheet can populate the cells relating to how many hours the entitlement is worth
    and how much wage is worth?

    An example of what im trying to add is on the sheet.

    Also Sundays are double time all day for all hours, but the sheet is taking into account time+half and double time hours when it doesn't need to.

    Any help with this will be greatly appreciated,

    Michael
    Attached Files Attached Files
    Last edited by beitzy; 11-14-2013 at 06:42 PM. Reason: wrong xls file

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Timesheet formulas that include leave entitlements

    Could you give more details what is what on your spreadsheet?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Hi,
    Thanks for your reply.

    Hourly rates are highlighted yellow which are adjustable, when a start time is selected the corresponding rate populates the hour rate field highlighted
    in green. When a finish time is selected the hours of work are automatically populated as single, time & half and double time. Tea relates to an allowance
    paid after 1 hour overtime which also automatically updates. All add up to a monetary value.
    What I would like to include is the leave entitlements I have typed under the yellow highlighted box and be able to adjust the hourly rate
    accordingly. To the left of these are the hours paid for each entitlement.

    This is where I need assistance.
    If no times are selected remaining cells are zeros, which is fine, but I would like to be able to select an entitlement and have the corresponding
    hours, rate and monetary value automatically fill the cells.

    The other issue is Saturday and Sunday are double time all day, for all hours worked, but if overtime on these days are selected time & half and
    double time cells are still populated.

    I hope I have been able to explain myself.
    I appreciate your help,

    Michael

  4. #4
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Hi All,
    Just wondering has anyone got any ideas for the formulas I need?
    Please, I'm stuck.

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    Just need some clarification here, If a person worked on a regular day off, they get paid for 8 hrs at 20$/hr calculated as double time (8 hrs * 40$/hr)minimum or hrs worked @20$/hr @ double time, or whichever is larger ?
    if it is a sick day (or other listed day) they get 7.6 hrs at 20$/hr calculated at regular time (7.6 hrs *20$/hr) ?
    and sunday is double time for hrs worked, or does the 8hrs minimum come into affect here as well?
    Last edited by dredwolf; 11-19-2013 at 02:20 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  6. #6
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Worked on a regular day off is double time for all hours worked, this is the same for Saturday and Sunday. I have tinkered with the formulas for the weekends and seems to work.
    Sick day (or other listed day), what you have stated is correct.
    I have attached an updated spreadsheet.
    Attached Files Attached Files

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    okay, and if rdo is sat sun, no change, just double time? that's what I'm going to assume anyways

    Edit -
    and if I remember right, Saturday was only time and a half first time we went through this...???
    Edit - 2
    I have noticed that sometimes, just asking questions like these will give the OP ideas so they can come up with their own solutions, it does not mean I don't work on it, just that you do too, and sometimes you come up with better solutions than I can, because you understand what you are working with, where I am, in a sense working "blind"
    Last edited by dredwolf; 11-20-2013 at 01:21 AM.

  8. #8
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Yep worked rdo on sat, sun just double time, no change.
    my mistake, Saturday is not time and a half, its double all day.
    Im glad to work on the spreadsheet where I can, my problem is complex formulas just don't happen for me lol.

    What I tried was to include rdo, sick, etc into the sheet, use them like entering times and once selected the cells would populate them selves.

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    No problem !
    Our hours seem to be near opposite though ( weird for Australia and BC, Canada), so if I mess this up, please do not be too impatient, lol

  10. #10
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    you are doing me an absolute huge favour, much appreciated too!
    let me know if I can help any way I can.

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    oh, almost forgot, is a worked RDO got the minimum of 8 hrs or just worked time?

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    bleah, missed that post...just time worked..give me a bit here

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    okay, I made the following changes in your first upload
    F3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    changes reg hours based on weekday or the note in column C

    I3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    new tea money calculation, so that the changes in column E do not mess with this

    N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    gets hourly rate based on C column note, or, if no note, start time

    Extend these down to row 16

    See Attached

    Hope this helps
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Hi Dredwolf,
    What you have done is spot on, thankyou.
    Only thing I picked out and tried to see if I could fix(but couldn't),
    was when the hours of worked are selected along with WKD RDO the total amount of hours worked disappears.

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    WKD RDO hours show up under column K (as wkd rdo is all double time), so they haven't disappeared, they do the same as sat or sun hrs, this is one of the reasons I had to change the tea money formula

  16. #16
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    my mistake...I think I might need glasses, I checked the whole row and didn't see it
    can I bug you for one more thing? need to include public holiday also.
    I gave it a shot but it doesn't seem as easy as telling the formula to look at the extra cells for public holiday.
    The leave entitlements don't work on weekends for some reason?
    And the weekend pay rate is the same as wkdrdo @ double time, the rate wont change depending on start time.
    Im sorry to cause confusion, but these things aren't always seen straight away.
    Last edited by beitzy; 11-21-2013 at 05:36 AM. Reason: amend post

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    okay, how does public holiday rules work? 7.6 hrs at regular time if not worked, #hrs worked @ double time if worked ?
    Edit- and what pay rate?

  18. #18
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    8 hours pay at regular time if not worked, rate is 2.5 * regular time if worked for all hours worked

  19. #19
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    And the pay rate?

  20. #20
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    Try Attached (I've assumed $20/hour),
    modified columns E & N to take into account new option, and modified column L to use 2.5 times for PB HOL calculation
    also added PB HOL to your Drop down list, and table for hours and rate
    Hope this helps
    Attached Files Attached Files

  21. #21
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    I think I'm going blind too, did not see the other issues, I think I assumed that weekends were exempt from leave entitlements, so I'll correct that, as to weekend pay rate, you want it to equal the same as WKD RDO ?
    as it is, it does change for start time, but I can modify for weekends to be same as WKD RDO

  22. #22
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    See attached
    First one simply corrects for leave entitlement on weekends, second forces weekends to use worked RDO pay rate instead of start time pay rate
    Hope this helps

  23. #23
    Registered User
    Join Date
    08-16-2011
    Location
    australia
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Timesheet formulas that include leave entitlements

    Dredwolf, Thankyou so much for your help. I really appreciate it.
    The spreadsheet is exactly what I was looking for and works great.

  24. #24
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Timesheet formulas that include leave entitlements

    You are welcome !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Holiday Entitlements
    By charliebeth in forum Excel General
    Replies: 2
    Last Post: 12-12-2011, 10:32 AM
  2. Formulas for calculating timesheet
    By Val C in forum Excel General
    Replies: 4
    Last Post: 05-25-2010, 07:31 AM
  3. Formulas for timesheet
    By draftedsolution in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2007, 04:11 AM
  4. Time Calculation For A Timesheet To Include Lunch
    By poddys in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2006, 04:10 PM
  5. [SOLVED] Timesheet formulas
    By Doug in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2005, 04:06 PM

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