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))

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

        HansVogelaar, Yes that works great, Thank You - Matt

Resources