Calculating Rate hours for late shift running after midnight

[Sullster47]

I am stuck on this and would love some help. I have a customer who has 3 rates of pay and personnel can in theory go across all three. Working out the rates 1 & 2 are simple as they are in the same day but the 3rd rate goes across midnight. Rate 2 starts at 18:00 and runs till 00:00(24:00). The code below works until we cross this time and then it fails. When I ask the question: is the end time later than rate 3 start time (which means I need to move some hours into the 3rd rate) it gives the wrong answer.
Any help appreciated and gratefully received.

If CDBreak!CDPTMF2 = True Then 'section 1
    'here get the rate time start and rate 2 start for the end time
    RateTimeStart = CDBreak!CDPTTime2
    RateTimeEnd = CDBreak!CDPTTime3
         If ThisTimeIn >= RateTimeStart Then 'And ThisTimeIn <= RateTimeEnd Then ' WE HAVE THE RIGHT START RATE
        'section 2
            'we have the corect rate slot
            If ThisTimeOut > RateTimeEnd Then 'check to see if we need to go into another time slot
                  ' we now need to split the worked hours into the rate 1 and rate 2
                    Bookings.Edit
                    'need to find how many hours in rate 2 and then minus it from the total hours
                    Bookings!BRate1Hrs = Left(RateTimeEnd, 2) - Left(Bookings!BTimeIn, 2)
                    Bookings!BRate2Hrs = Bookings!BHours - Bookings!BRate1Hrs
                    Bookings.Update
                    MsgBox "Rate 1 - " & Bookings!BRate1Hrs & " Hrs & Rate 2 - " & Bookings!BRate2Hrs & " Hrs"
                    Else
                    'put all hours in the Rate 1 box
                    Bookings.Edit
                    Bookings!BRate1Hrs = Bookings!BHours
                    MsgBox "Rate 1 - " & Bookings!BRate1Hrs & " Hrs"
                    Bookings.Update
                     End If
                   AllSorted = True
                
            End If
    End If

[NeoPa]

I tried to understand your explanation and tie it up in some way with the code but I stopped when my head hurt. I couldn't understand the explanation and the code had no discernable correlation I could see so that was no help.

[Sullster47]

Argh the ramblings of a programmer mid-flow.
Apologies for this.
I am using the start time of the next rate to work out the end of the current rate. This way I dont hold duplicate data.
All works well until a shift goes across the date line into the next day.
I dont know how to add a day on to the shift time to work out the hours worked.

[NeoPa]

Yes. I feel you (In a modern way of terminology you understand).

In very simple terms, that deal directly with your question, the code you need in order to add a day to any Date/Time value (and that can perfectly validly be a simple Time value with no date component) is to use DateAdd. The example I show uses named parameters which I tend to prefer, but it can equally be used with the parameters specified positionally :

ResultValue = DateAdd(Interval:="d", Number:=1, Date:=OriginalValue)

[Sullster47]

Thanks. I have combined the Booking Date field with the Booking Time In field which gives me the correct format date so that when I do the same with the TimeOut, I can add a day if the TimeOut is less than the TimeIn (04:00 < 20:00).
This now works to give me the 04:00 in the next day but how do I then work out how many hours between MyTimeOut and MyTimeIn?
MyTimeIn = Bookings!MTIn (returns 07/02/2023 20:00)
MyTimeOut = DateAdd(Interva l:="d", Number:=1, Date:=Bookings! MTOut) (returns 08/02/2023 04:00)

forgive me if this is an easy answer but I am totally fried.

[Sullster47]

MyStr = DateDiff("h", MyTimeIn, MyTimeOut)
This returns the correct value.
NeoPa, thank you so much for your help. You have been a massive help and I am deeply indebted to you.
Thank you

[NeoPa]

I'm very happy to help (A mamoth waste of my time if I weren't obvs ;-)).

Do bear in mind that whole hours are easy to count when the values are constrained to the same time in the hour (EG. 04:00, 06:00, etc.), but when they differ things can get more complicated. Then you'd use "s" for seconds or "n" for minutes - depending on the granularity you require.

[Sullster47]

Yes I have found an added issue as I have to take their breaks off the final time as well. Now I have the easy ones done, I have to work out the hard one where the rate paid depends on which time band it falls into and they often fall into 3 bands! I am working through it but couldn't have done it without your input.

[NeoPa]

No problem. As I say - very happy to help :-)

I recently worked with a company (Nurseplus UK Ltd - who provide caring resources across GB.) who avoided that issue by using only the start time to determine rates. When it was important they could split the shifts into two so that each block was calculated based on its own applicable rate.

This may not suit your situation of course, but I offer it as a simple idea. Simple ideas are often overlooked ;-)

Otherwise happy to work with you on a procedure that handles multiple rates within a shift. If desired though, please post as a separate question.