Forum Discussion

mmilbourne's avatar
mmilbourne
Copper Contributor
Jan 17, 2023

Overtime calculations based on different rates.

Good Morning, I need to create a spreadsheet to track hours worked and overtime, however each job worked could have a different base rate and a different time that overtime starts after. for example...
excel
office 365
mmilbourne's avatar
mmilbourne
Copper Contributor
Jan 28, 2023

HansVogelaar 

Thank You! that worked great, 

I do have one other question; I have attached the sheet I am working with. 

I need to be able to calculate the time spent in a hotel (held away) on a round trip it is the end time of the previous day and the start time of the next day.

 

Thank You.

Matt M.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 29, 2023

    mmilbourne 

    Can you explain this in more detail, referring to the workbook that you attached?

    • mmilbourne's avatar
      mmilbourne
      Copper Contributor
      Feb 02, 2023

      I need to be able to calculate the time between off duty (column n) at any location other than BI 128 (column m), and the on-duty time the next (column l) day if over 16:00 hours. if that makes sense. it's to calculate the time spent in a hotel (held away time). so a round trip is from BI 128 to BI 128 with a stop at any other location.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 02, 2023

        mmilbourne 

        Does this do what you want?

         

        =IF([@[Off Duty Time]]="BI 128",0,MAX(OFFSET([@Date],1,0)+OFFSET([@[On Duty Place]],1,0)-[@Date]-[@[Off Duty Place]]-TIME(16,0,0),0))

Resources