+ Reply to Thread
Results 1 to 6 of 6

T.I.L. hours on Timesheet

  1. #1
    Registered User
    Join Date
    05-06-2008
    Posts
    31

    T.I.L. hours on Timesheet

    I am trying to enter correct formula for T.I.L. hours on Timesheet that contains see below. Please find Excel worksheet attached. Please refer to green highlights in the attachment which includes for your information and it explains how it works.

    "Normal Operating Hours" 8.00am to 6.00pm Monday to Friday.
    "Normal Start and Finish Times" 9.00am to 5.06pm
    "Ordinary hours" 38 hours per week
    "Length of an Ordinatory working day" 7.36 hours or 7.6 hours in decimal plus an unpaid 30 minute meal break
    "Meal and Rest break" Employees will not be required to work more than 5 hours without an unpaid meal beak of at least 30 minutes duration

    1.5 hours of TIL for each of the first 2 hours of overtime worked then 2 hours of TIL for every overtime hour worked (Monday - Saturday)
    For time worked on Sunday 2 hours of TIL for every hour worked

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: T.I.L. hours on Timesheet

    Maybe I'm not reading it correctly but your question:

    If I see you timesheet it covers a 2-week period starting a Monday and ending the last Sunday of the following week.
    Each day the time is entered and the person either works the allocated hours for that day so nothing special there.
    What you are asking (I think) is that you want to calculate how many hours extra this person has worked (overtime) for a particular day and if Mon-Sat add 1.5 hours for each first 2 hours and then 2 hours for the remaining hours to the TIL time
    For a Sunday always add 2 hours to TIL for each overtime
    Is this correct?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: T.I.L. hours on Timesheet

    In the Hours Minutes column J you still left some formulas is this intentional? Or an error on your part?
    Rows 17, 24 and 25

  4. #4
    Registered User
    Join Date
    05-06-2008
    Posts
    31

    Re: T.I.L. hours on Timesheet

    In regards to your messages, you are correct, however, in addition, the normal hours for Monday to Friday is 8.00am to 6.00pm, if the person works overtime either between 8.00 to 9.00am and/or 5.06 to 600pm, add the TIL time of 1.0 hour instead of 1.5 hours. Earlier than 8.00am or after 6.00pm, add 1.5hours hours for each first 2 hours and then 2 hours for the remaining hours to the TIL time. I am looking for the accurate formula on Column I12 to I25.

  5. #5
    Registered User
    Join Date
    05-06-2008
    Posts
    31

    Re: T.I.L. hours on Timesheet

    Yes you are correct it is an error on J17, 24 & 25. It should read 0:00. Thanks for this.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: T.I.L. hours on Timesheet

    I did a quick and dirty one.
    I inserted a column J and the formula there for the overtime
    The addition is not all correct yet will require some fine-tuning. Once that is dome you can just hide column J
    See if it makes sense
    Attached Files Attached Files

+ 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] Timesheet Hours - calculate Ord and O/T hours
    By moongazer208 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2015, 04:06 AM
  3. Timesheet number of hours
    By snottus in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-29-2014, 04:34 AM
  4. Need help with weekly timesheet to round all hours to nearest 1/4 hours
    By markjoel777 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-16-2013, 12:19 AM
  5. [SOLVED] Regular Hours on Timesheet
    By Meep in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 02:14 AM
  6. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  7. Calculate Add'l Hours on Timesheet per day
    By FMGSCG in forum Excel General
    Replies: 7
    Last Post: 04-29-2010, 01:47 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