How to calculate a finish time

[jacc14]

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.

[ADezii]

@jacc14

1. 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

2. Call the Function:
   

   MsgBox "The Finish Date/Time for this Project is: " & fCalcFinish(3000, #5/28/2010 6:30:00 PM#)

3. View the Results (in a Message Box):
   

   The Finish Date/Time for this Project is: 5/28/2010 8:00:00 PM

4. Any questions, feel free to ask.

[jacc14]

@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

[ADezii]

@jacc14

1. Hello Christine, would there ever be a case where the Start Date and End Date are not the same?
2. Would the Time Differential ever span Midnight?

[ADezii]

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

[jacc14]

@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.

[ADezii]

@jacc14
The code in Post #5 will work, given the Post-Midnight scenario.

[jacc14]

@ADezii
Exactly what i am looking for . It works a treat.

many thanks
Christine.

[ADezii]

@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:

1. The Number of Copies should be > 0 and <= 32,767, the Maximum for an Integer.
2. If you want a Maximum Number of Copies, and the User enters a Value beyond that, then reset to the Max.
3. Make sure that the Start Date is actually a Date using IsDate().
4. Make sure that the Start Time is actually a Date using IsDate().
5. YaDa, YaDa, YaDa...

[jacc14]

@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.

[jacc14]

Hi.

Think I have got it. Its calculating seconds??

Christine.

[ADezii]

@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

[jacc14]

@ADezii
Thanks for your help with this. Result.

[ADezii]

@jacc14
You are quite welcome.