How to calculate a finish time

**ADezii** · May 29 '10, 01:17 PM

Originally posted by 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.

Create a Public Function to do the work:

Code:

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:

Code:

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

Code:

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

Any questions, feel free to ask.

**jacc14** · May 29 '10, 02:22 PM

Originally posted by ADezii

Create a Public Function to do the work:

Code:

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:

Code:

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

Code:

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

Any questions, feel free to ask.

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** · May 29 '10, 04:31 PM

Originally posted by jacc14

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

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?

**ADezii** · May 29 '10, 07:47 PM

I have arrived at a sort of convoluted Solution. I would wait and see if someone comes up with something better.

Code:

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

Code:

'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

Code:

Finish Date: 05/28/2010
Finish Time: 8:00 PM

Code:

'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

Code:

Finish Date: 05/29/2010
Finish Time: 4:30 AM

**jacc14** · May 29 '10, 09:36 PM

Originally posted by ADezii

I have arrived at a sort of convoluted Solution. I would wait and see if someone comes up with something better.

Code:

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

Code:

'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

Code:

Finish Date: 05/28/2010
Finish Time: 8:00 PM

Code:

'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

Code:

Finish Date: 05/29/2010
Finish Time: 4:30 AM

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** · May 29 '10, 10:20 PM

Originally posted by jacc14

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.

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

**jacc14** · May 30 '10, 08:11 AM

Originally posted by ADezii

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

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

many thanks
Christine.

**ADezii** · May 30 '10, 01:54 PM

Originally posted by jacc14

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

many thanks
Christine.

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

**jacc14** · May 31 '10, 09:41 PM

Originally posted by ADezii

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

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:

Code:

 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.0104166666715 173
01:00:00 produces 0.0416666666642 413

Any ideas please

Thanks
Christine.

**jacc14** · May 31 '10, 09:52 PM

Hi.

Think I have got it. Its calculating seconds??

Christine.

**ADezii** · May 31 '10, 10:16 PM

Originally posted by jacc14

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:

Code:

 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.0104166666715 173
01:00:00 produces 0.0416666666642 413

Any ideas please

Thanks
Christine.

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

Code:

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** · Jun 1 '10, 06:50 AM

Originally posted by ADezii

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

Code:

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

Thanks for your help with this. Result.

**ADezii** · Jun 1 '10, 11:46 AM

Originally posted by jacc14

Thanks for your help with this. Result.

You are quite welcome.

How to calculate a finish time

How to calculate a finish time

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment