Formula to calculate hours worked w/lunch or w/o + OT only after 12.25 hours for day


  • I am working on a timecard worksheet that calculates the time worked between the start time and end time. I can get it to calculate and round to the nearest quarter hour, but I also want it to max out at 12.25 hours than its OT. This is what I have so far:

    =ROUND((D45-C45)*24,1) for regular pay

    =IFERROR(MROUND((D45-C45+F45-E45+(C45>D45))*24-12.25,0.25),0) for my OT 

    2018년 4월 17일 화요일 오후 2:28

모든 응답

  • Hi Blinhart,

    I read through your post, but am not quite sure about your requirement without a sample file. Could you please remove the privacy information and then share a sample via OneDrive then our community members can help you better? Or you may copy and paste a sample table here for elaboration. 


    Yuki Sun

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 4월 18일 수요일 오전 2:44