+ Reply to Thread
Results 1 to 6 of 6

Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

  1. #1
    Registered User
    Join Date
    04-14-2013
    Location
    Melbourne,Australia
    MS-Off Ver
    office365
    Posts
    4

    Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    I need help in getting a formula to work in overtime H8 to H13 column, where it takes any hours worked over 8 hours into the overtime hours. Into Time 1/2 (T1/2) cell and double time (DT) cell.
    Where any overtime is transferred as the first two hours are @ Time & 1/2 - I8:I13, the rest of the overtime hours are put into cells J8:J13 as double time.
    All day Saturday, the first two hours worked on a Saturday is Time & 1/2 (T1/2), then the rest of the hours worked on a Saturday are double time (DT)
    There is no trading on a Sunday. Is it possible to have an IF or nested IF formula that works in these cells?
    I have attached the excel table that I am struggling with.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    ugh image for spreadsheet
    Attachment 228933

    Change the format of time to actual numbers
    so 7:30 = 7.5

    if you need to work in time, you need to figure out the fractions...you can do that yourself
    the formulas work to what you have described

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    Hi bev and welcome to the forum

    why would you paste a picture of your worksheet into a worksheet, and then upload that? why not post the actual sample workbook? A picture is impossible to work with, and no-one wants to re-type your data for you

    Please upload the actual sample workbook (with no sensitive info)

    @ humding, disagree with changing the time format to numbers - time is time and should be calc'd as such, where all time is a fraction of 1 (day)
    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

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    redone with time

    was tied down this morning to do it proper
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-14-2013
    Location
    Melbourne,Australia
    MS-Off Ver
    office365
    Posts
    4

    Re: Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    Thanks for the welcome and thanks for your assistance.
    I suppose it's the old saying, keep it simple. When you put too much into it, you lose it!
    O.k, the time format option, and fractions gives more detail which also makes a huge difference, in all it's scenarios... which I am learning.

    Thanks for helping. Thanks heaps!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells

    Happy to help and thanks for the feedback

+ 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