473,289 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Calculating Rate hours for late shift running after midnight

5 Nibble
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.
Expand|Select|Wrap|Line Numbers
  1. If CDBreak!CDPTMF2 = True Then 'section 1
  2.     'here get the rate time start and rate 2 start for the end time
  3.     RateTimeStart = CDBreak!CDPTTime2
  4.     RateTimeEnd = CDBreak!CDPTTime3
  5.          If ThisTimeIn >= RateTimeStart Then 'And ThisTimeIn <= RateTimeEnd Then ' WE HAVE THE RIGHT START RATE
  6.         'section 2
  7.             'we have the corect rate slot
  8.             If ThisTimeOut > RateTimeEnd Then 'check to see if we need to go into another time slot
  9.                   ' we now need to split the worked hours into the rate 1 and rate 2
  10.                     Bookings.Edit
  11.                     'need to find how many hours in rate 2 and then minus it from the total hours
  12.                     Bookings!BRate1Hrs = Left(RateTimeEnd, 2) - Left(Bookings!BTimeIn, 2)
  13.                     Bookings!BRate2Hrs = Bookings!BHours - Bookings!BRate1Hrs
  14.                     Bookings.Update
  15.                     MsgBox "Rate 1 - " & Bookings!BRate1Hrs & " Hrs & Rate 2 - " & Bookings!BRate2Hrs & " Hrs"
  16.                     Else
  17.                     'put all hours in the Rate 1 box
  18.                     Bookings.Edit
  19.                     Bookings!BRate1Hrs = Bookings!BHours
  20.                     MsgBox "Rate 1 - " & Bookings!BRate1Hrs & " Hrs"
  21.                     Bookings.Update
  22.                      End If
  23.                    AllSorted = True
  24.  
  25.             End If
  26.     End If
Jan 31 '23 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. ResultValue = DateAdd(Interval:="d", Number:=1, Date:=OriginalValue)

8 8442
NeoPa
32,554 Expert Mod 16PB
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.
Feb 1 '23 #2
Sullster47
5 Nibble
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.
Feb 7 '23 #3
NeoPa
32,554 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. ResultValue = DateAdd(Interval:="d", Number:=1, Date:=OriginalValue)
Feb 7 '23 #4
Sullster47
5 Nibble
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(Interval:="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.
Feb 7 '23 #5
Sullster47
5 Nibble
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
Feb 7 '23 #6
NeoPa
32,554 Expert Mod 16PB
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.
Feb 7 '23 #7
Sullster47
5 Nibble
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.
Feb 8 '23 #8
NeoPa
32,554 Expert Mod 16PB
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.
Feb 8 '23 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: ????? | last post by:
I have a query which shows the following fields and orders by every one in turn from left to right in ascending order. The fields are DATE, , WORK, *, DESCRIPTION and . How can I get a running...
2
by: mtech1 | last post by:
Access 2003 I need to create a query to view data by 3 - 24 hour shifts. Ex: There are 3 shifts, each shift is 24 hours starting at 0700 through 0700. Each shift is designated as A, B, or C. ...
3
by: Amy Smith | last post by:
Hello there, I am having a small problem which been challenging me for few days and need help or advice. I am trying to calculate the day-shift for employees based on the time they started and...
8
by: hmmtn | last post by:
I would appreciate any help on this one. Assignment is as follows: Develop a program that will determine the gross pay for each of several employees. The company pays "straight time" for the first...
3
by: Dan | last post by:
I'm trying to calculate the total work hours between a start and end date, and also working out an end date by adding a number of work hours to a start date. Can anyone recommend a decent formula...
4
HaLo2FrEeEk
by: HaLo2FrEeEk | last post by:
I'm trying to write a program for myself, allowing me to enter my schedule for work and have it calculate the total hours for the week. I'm storing the hours and minutes for each day as a byte,...
1
by: titli | last post by:
Hi All, I have the following in every column as shown below.Could you please let me know the formala for calculating the hours between two dates with times ColumnK2 ColumnL2 ...
0
by: tnynyn | last post by:
I have created a form with two datetimepicker showing only hours (start and end times). I have timespan calculating the total hours, but when it goes to the next day, it adds 24 hours. For example,...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.