+ Reply to Thread
Results 1 to 15 of 15

Formula for Timesheet - Double Time, Time Half and normal Hours

  1. #1
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Formula for Timesheet - Double Time, Time Half and normal Hours

    Hi,

    Just wanted to get some feedback on formulas for the new time sheet i am developing for a company.

    I have attached a make shift one generated but i am at a blank at how to work out for time and a half and double time.

    Quick overview is

    Monday to Friday -
    8 hours is at the normal RATE, first two hours after that is time and half and anytime from there is double time
    SATURDAY
    First 2 hours is time and half and anytime from there is double time
    SUNDAYTIMESHEETS (2).xlsxTIMESHEETS (2).xlsx
    All hours are double time

    I will not be importing RATES at all as this is for the staff to fill in there whole hours for the day and i want to get it to manually gfenerate down the bottom so i have the amount of hours in the end.
    I am happy to have to hide columns etc.

    Any help would be great.

    Thank you

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    This looks scarily similar to the problem in the link below, where a solution has been provided.

    http://www.excelforum.com/excel-form...ime-2-0-a.html

  3. #3
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Hi Quekbc,

    thank you for the reply.

    That is very 'scarily similar'. To mine. But one difference is that i do not get the time its self imported in by the staff. I only get them to insert the amount of hours worked as a whole number as they may do multiple jobs in the one day at different places

    Any help would be appreciated. i have attached again.

    Thank you again for the reply.TIMESHEETS (2).xlsx

  4. #4
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Not a problem.

    I notice that the days on row 4 are typed in as WEN, THUR, FRI, SAT, SUN, MON, TUE in that order. Is this always the case (i.e. consistent)? Or will they change from timesheet to timesheet?

  5. #5
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Our working week starts on a Wednesday.

    I have filled around a little and this is the new sheet,

    but still at a blank to how to calculate it all.

    Thank you again!

    TIMESHEETS (version 1).xlsx

  6. #6
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    and sorry yes, that is always the case.

  7. #7
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Understood. So, I did it in 2 ways in the attached file, both runs the same idea, but the ones in column N:P are clearer for me to do a sense check (i.e. calculate the split row by row). These references within the formulas in columns N to P can be extended to include the whole table, as done in cells C45:C46.

    Feel free to choose one or the other.

    In the interest of sharing this information openly,

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by quekbc; 07-29-2015 at 02:40 AM.

  8. #8
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Hi Quekbc,

    I can not thank you enough. i will look over this tonight and wrap my head around it all.

    If i have any questions i will let you know in the morning.

    Thank you again!

  9. #9
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    this looks perfect. But looking at it again it seems to not actually do what i needed it to do. I think it’s a little more complex then I first imagined.

    my queries are;

    the daily totals in row 22 is where we would need to pull the info from for the formulas you have come up with, to equal in the same sections C32:34. (if you could please redo that for me – as I have tried to copy your formulas to different rows but it has not worked).

    That I think is this only way we will be able to at least get the totals correct at the bottom.

    The idea was to try and avoid the staff writing in their overtime hours in rows 26:30 – But we also need to determine if the worker has allocated overtime hours on any day from Wednesday to Tuesday to individual jobs as it will then need to be billed to that job.

    All in all overtime needs to be determined by day and by job on each day.

    I have attached a new sheet with all the details.

    Sorry for this, just want to make sure everything works.Copy of TIMESHEETS (version 1)_bc.xlsx

  10. #10
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Hi Tracs. I understand the bit about getting RDO, Sick days and annual leaves into part of the calculation; that part is easy (see formula/attached below). However, I am not getting the bit about the OVERTIME section (row 26:30). What do you want seen there?

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

  11. #11
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Example is
    On Wednesday – the staff member worked 15 hours. Therefore the staff member has worked 8 hours at normal time, 2 hours at time and half and 5 hours at double time. This needs to be worked out and shown (for all days) in rows 33:35.
    In rows 24:30 –
    Example shown –
    On Wednesday the staff member worked at 4 different jobs. As you can see in rows 6,7,9,11. In rows 6, 9,11 they are at normal rate. But in ROW 7 (job 39412) – the job itself accrued 5 hours of overtime from that one staff member also. So the job (job 39412) will need to be billed the extra money for the OT the staff member had to work on that given day
    -
    Column K is just there as a whole figure – to see how many hours have been worked in the one job for the week. But the figure itself is not used for anything much. As you cannot determine the normal hours/overtime worked as it is across 7 different days.
    --
    It is a complicated option. And i don’t think there is a way around it really. Unless we change the whole format of the time sheet itself and add a whole lot of other options in.
    ----
    Arranging the formula for rows 33:35. Might be the best option. And i will just get the staff to add in the overtime per job themselves.
    By looking at the current sheet. I can tell on this given week this staff member has done the below

    Normal Hours: 24
    OVERTIME HOURS - TIME ½: 6
    OVERTIME HOURS – DOUBLE: 14

    also (RDO: 8 & SICK: 8 ) - which is working out OK itself.

    Is there any chance we can make that be right?

    Sorry if this is all a pain to you.TIMESHEETS (version 1)_bc3.xlsx

  12. #12
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Okay, now I understand what you want there and I manage to get the 24/6/14 hours split that you mentioned above (see attached).

    The only problem now, from my view is the OVERTIME section.

    Why did you assume that Job 34912 is the one that should be incurred the overtime cost? The way I see it, is that the employee has worked on Wednesday, 15 hours. The overtime of 7 hours can be apportioned to any of them. Why just Job39412?

    Another example is, consider Friday. Total hours worked is 11 hours, an overtime of 3 hours. Should this be noted within the OVERTIME section? If not, why? If so, how should the overtime be apportioned? Full 3 hours of Job39412? Or should it be "random" at row 10.

    Please know that I'm asking these questions so I know which labels to pull into the OVERTIME section on cells A27:C31.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-28-2015
    Location
    Australia
    MS-Off Ver
    2007
    Posts
    8

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Hi,
    Sorry i have been on holidays and have just come back.

    I had a meeting yesterday with everyone for this sheet. They have raised a copy of issues. please see below comments and let me know if you think this can be arranged at all?

    Bottom section will be calculated at ratios 1.5 hours for each hour for the first 2 hours then 2 hours for each hour after the first 2 hours. Jobs need to be allocated overtime hours specifically due to staying back at that job only (eg: After 3:30 I stayed at the last job for the day which was 33465, an additional 3 hours overtime and then went home).

    The top section (normal/otdinary hours) each day will only ever have 8 hours total and will never have to be calculated at the ratio of 1.5 or 2 hours per 1 hour. (ie anything after 8 hours gets put into the overtime section)

    Let me know your thoughts ..


    thank you again!

  14. #14
    Registered User
    Join Date
    01-27-2013
    Location
    Kuurman, South Africa
    MS-Off Ver
    Excel 2016 Professional
    Posts
    17

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    I want to get this thread back up again:
    I want to do a Timesheet workbook in Excel 2016.
    The timesheet is for a group of people.
    South African Basic Employment act prescribe overtime, double time and one-and-half time, night shift allowance. And I want to calculate them all in one sheet.
    Because I cannot find a similar solution on the Internet, am I asking the question here.
    The help that I need is with the formula to calculate NightShiftHours, DoubleTime and Overtime.
    NightShiftHours = work between 18:00 and 6:00.
    DoubleTime = work on Sunday and Public Holidays
    Overtime = time and half for more work than 9 hours per day.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Formula for Timesheet - Double Time, Time Half and normal Hours

    Hello LouisH,
    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 this thread is particularly relevant to your need, provide a link to this thread in your new thread.

    Old threads (this one is over two years old) 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.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. [SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week
    By KazzICC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 01:51 AM
  3. Replies: 2
    Last Post: 03-07-2013, 09:29 PM
  4. Replies: 17
    Last Post: 11-15-2012, 06:12 PM
  5. double time formula for timesheet
    By techmob in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2009, 11:21 AM
  6. Calculating Time and a Half and Double Time (After 40 hours)
    By DoreenBassett in forum Excel General
    Replies: 4
    Last Post: 02-23-2009, 08:10 AM
  7. Calculating Time and Half and Double Time (After 40 hours worked)
    By DoreenBassett in forum Word Formatting & General
    Replies: 1
    Last Post: 02-20-2009, 10:01 AM
  8. Timesheet problem:distinguishes between Normal Time and Overtime,
    By melba in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-08-2007, 06:21 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