I have a magazine that prints 2000 copies an hour. Therefore if i have a start date of 28/05/10 and start time of 18:30 and wish to produce 3000 copies I want to return 28/05/10 in the finish date and 20:00 in the finish time as an estimate of the finish time. Can anyone advice me on this.
I have arrived at a sort of convoluted Solution. I would wait and see if someone comes up with something better. -
Public Function fCalcFinish(intNumOfCopies As Integer, dteStartDate As Date, dteStartTime As Date) As String
-
Dim dteStartDateAndTime As Date
-
Dim dteFinishDateAndTime As Date
-
Const conCOPIES_PER_HOUR As Integer = 2000
-
-
'Combine Start Date and Time into a Single Component
-
dteStartDateAndTime = CDate(Format$(dteStartDate, "mm/dd/yyyy") & " " & dteStartTime)
-
-
'Add the required Number of Minutes based on the Number of Copies requested to the
-
'combinied Start Date and Time
-
dteFinishDateAndTime = DateAdd("n", (intNumOfCopies / conCOPIES_PER_HOUR) * 60, dteStartDateAndTime)
-
-
'Break Finish Date/Time down into 2 seperate Components, separated by a Comma
-
fCalcFinish = Format$(dteFinishDateAndTime, "mm/dd/yyyy") & "," & _
-
Format$(dteFinishDateAndTime, "h:nn AM/PM")
-
End Function
- 'Use the Split() Function to retrieve both the Date (1st Element of the Array),
-
'and the Time which would be the 2nd Element of the Array
-
Debug.Print "Finish Date: " & Split(fCalcFinish(3000, #5/28/2010#, #6:30:00 PM#), ",")(0)
-
Debug.Print "Finish Time: " & Split(fCalcFinish(3000, #5/28/2010#, #6:30:00 PM#), ",")(1)
produces - Finish Date: 05/28/2010
-
Finish Time: 8:00 PM
- 'Use the Split() Function to retrieve both the Date (1st Element of the Array),
-
'and the Time which would be the 2nd Element of the Array
-
Debug.Print "Finish Date: " & Split(fCalcFinish(20000, #5/28/2010#, #6:30:00 PM#), ",")(0)
-
Debug.Print "Finish Time: " & Split(fCalcFinish(20000, #5/28/2010#, #6:30:00 PM#), ",")(1)
produces - Finish Date: 05/29/2010
-
Finish Time: 4:30 AM
13 2887 @jacc14 - Create a Public Function to do the work:
- Public Function fCalcFinish(intNumOfCopies As Integer, dteStartDate As Date) As Date
-
'Pass to this Function the Number of Copies desired, as well as the Start
-
'Date/Time. Based on 2,000 Copies/Hr., it will then add the required Number
-
'of Minutes to the Start Date/Time and return the Finish Date/Time
-
fCalcFinish = DateAdd("n", (intNumOfCopies / 2000) * 60, dteStartDate)
-
End Function
- Call the Function:
- MsgBox "The Finish Date/Time for this Project is: " & fCalcFinish(3000, #5/28/2010 6:30:00 PM#)
- View the Results (in a Message Box):
- The Finish Date/Time for this Project is: 5/28/2010 8:00:00 PM
- Any questions, feel free to ask.
@ADezii
Hi thank you for this. unfortunately i have had to split out the date and time fields as the user is required to put in times and i dont want him to enter a date everytime as well . Would this be possible. I therefore have 4 fields.
start date start time finish date finish time.
I would want my results posting to the last 2 fields using the no of copies per hour and start date and time
Best regards
Christine
@jacc14 - Hello Christine, would there ever be a case where the Start Date and End Date are not the same?
- Would the Time Differential ever span Midnight?
I have arrived at a sort of convoluted Solution. I would wait and see if someone comes up with something better. -
Public Function fCalcFinish(intNumOfCopies As Integer, dteStartDate As Date, dteStartTime As Date) As String
-
Dim dteStartDateAndTime As Date
-
Dim dteFinishDateAndTime As Date
-
Const conCOPIES_PER_HOUR As Integer = 2000
-
-
'Combine Start Date and Time into a Single Component
-
dteStartDateAndTime = CDate(Format$(dteStartDate, "mm/dd/yyyy") & " " & dteStartTime)
-
-
'Add the required Number of Minutes based on the Number of Copies requested to the
-
'combinied Start Date and Time
-
dteFinishDateAndTime = DateAdd("n", (intNumOfCopies / conCOPIES_PER_HOUR) * 60, dteStartDateAndTime)
-
-
'Break Finish Date/Time down into 2 seperate Components, separated by a Comma
-
fCalcFinish = Format$(dteFinishDateAndTime, "mm/dd/yyyy") & "," & _
-
Format$(dteFinishDateAndTime, "h:nn AM/PM")
-
End Function
- 'Use the Split() Function to retrieve both the Date (1st Element of the Array),
-
'and the Time which would be the 2nd Element of the Array
-
Debug.Print "Finish Date: " & Split(fCalcFinish(3000, #5/28/2010#, #6:30:00 PM#), ",")(0)
-
Debug.Print "Finish Time: " & Split(fCalcFinish(3000, #5/28/2010#, #6:30:00 PM#), ",")(1)
produces - Finish Date: 05/28/2010
-
Finish Time: 8:00 PM
- 'Use the Split() Function to retrieve both the Date (1st Element of the Array),
-
'and the Time which would be the 2nd Element of the Array
-
Debug.Print "Finish Date: " & Split(fCalcFinish(20000, #5/28/2010#, #6:30:00 PM#), ",")(0)
-
Debug.Print "Finish Time: " & Split(fCalcFinish(20000, #5/28/2010#, #6:30:00 PM#), ",")(1)
produces - Finish Date: 05/29/2010
-
Finish Time: 4:30 AM
@ADezii
Thank you for that I will give it a go. In answer to the early question, yes it could go over midnight which is where i hit a problem.
Cheers
Christine.
@jacc14
The code in Post #5 will work, given the Post-Midnight scenario.
@ADezii
Exactly what i am looking for . It works a treat.
many thanks
Christine.
@jacc14
Just as a side note, Christine, you should perform some kind of Validation on the Arguments before you pass them to the Function, such as: - The Number of Copies should be > 0 and <= 32,767, the Maximum for an Integer.
- If you want a Maximum Number of Copies, and the User enters a Value beyond that, then reset to the Max.
- Make sure that the Start Date is actually a Date using IsDate().
- Make sure that the Start Time is actually a Date using IsDate().
- YaDa, YaDa, YaDa...
@ADezii
Hello ADezii
Thank you for your advice. Wonderful. However I wanted to go further and have been experimenting with the code you have sent me.
I am basically wanting to now taken the duration to work back a machine speed. To do this I need the duration to be converted as a decimal. Adapting your code i did the following: - Public Function duration(dteStartDate As Date, dteStartTime As Date, dtefinishDate As Date, dtefinishTime As Date) As Double
-
-
Dim dteStartDateAndTime As Date
-
Dim dteFinishDateAndTime As Date
-
-
dteStartDateAndTime = CDate(Format$(dteStartDate, "dd/mm/yyyy") & " " & dteStartTime)
-
dteFinishDateAndTime = CDate(Format$(dtefinishDate, "dd/mm/yyyy") & " " & dtefinishTime)
-
-
duration = dteFinishDateAndTime - dteStartDateAndTime
-
-
-
End Function
-
I dont actually get the results I want and am not sure what i am doing. the results are as follows:
00:15:00 produces 0.0104166666715173
01:00:00 produces 0.0416666666642413
Any ideas please
Thanks
Christine.
Hi.
Think I have got it. Its calculating seconds??
Christine.
@jacc14
Sorry Christine, I'm still a little confused, but the following code will give you the Differential as a Fractional Component of a Day (1,440 mins): - Public Function duration(dteStartDate As Date, dteStartTime As Date, dtefinishDate As Date, dtefinishTime As Date) As Double
-
Dim dteStartDateAndTime As Date
-
Dim dteFinishDateAndTime As Date
-
Dim lngMinsDiff As Long
-
-
dteStartDateAndTime = CDate(Format$(dteStartDate, "mm/dd/yyyy") & " " & dteStartTime)
-
dteFinishDateAndTime = CDate(Format$(dtefinishDate, "mm/dd/yyyy") & " " & dtefinishTime)
-
-
'Calculate the Difference in Minutes
-
lngMinsDiff = DateDiff("n", dteStartDateAndTime, dteFinishDateAndTime)
-
-
'Return the Difference in Minutes as a Fraction of a Day rounded to 6 Decimal Places
-
duration = FormatNumber((lngMinsDiff / 1440), 6)
-
End Function
@ADezii
Thanks for your help with this. Result.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: shrishjain |
last post by:
Hi All,
I want to write a benchmark to calculate the time of each memory
access, in my program. Can someone give me an idea how to do that, or
may some software which I can use for the same.
...
|
by: Tim Quon |
last post by:
Hi
Is there any function to get the current time so I can calculate the
execution time of my code?
What all is in the time.h and sys/times.h?
Thanks
Tim
|
by: Qwert |
last post by:
Hello,
I do:
Debug.WriteLine("A: " & DateTime.Now.Ticks.ToString)
REM Calculate a bunch of stuff. Some loops and math functions.
Debug.WriteLine("B: " & DateTime.Now.Ticks.ToString)
but...
|
by: cvisal |
last post by:
Hi all
Im working on productivity calculations (Time calculations) and need
some help in coding.
Database Tool:MS-Access 2003.
The general operator punch-in time is 5:30 AM and the punch-out...
|
by: ret4rt |
last post by:
Hello.
I have a task to do which is as follows:
I have to do an "id query" on a primary key of my db and then calculate the response time of the query.
Task says that i'm able to take screenshots...
|
by: walt |
last post by:
Hello,
I have been trying to calculate the difference between two date and display the difference in hours and minutes (HH:MM). I can't get it calculate properly and I can't hours and minutes to...
|
by: Lancelot |
last post by:
Hello everyone. This is my first post here, but I've been looking for answer many time.
I have a crazy idea in my head and since I am quite a newby to python, I am not sure where to start or if...
|
by: John Torres |
last post by:
how do you calculate total time hours? I have sub total hours for each day and I've been trying to total all the hours. I'm getting odd numbers.
Thanks.
John
|
by: Guruganesh |
last post by:
i have to calculate the time diference of two file names namely
File 1: CBE03KMD_2010_03_08_23_06_36.upl
File 2: CBE03KMD_2010_03_08_22_11_47.upl
see the date is same but the time is also...
|
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"....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
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...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
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: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |