Time

[DUF]

I am working on a time and rate pay sheet
My problem is...... worktime starts at 00:18 and worktime ends at 09:30 a different rate of pay comes into effect at 07:00 to the end of work time how can i allow for that?
I use DateDiff("n", txtStart, txtEnd) / 60 to find the difference between the times but after 07;00 there is a different rate of pay

regards

Duf

 

[DrJavaJoe]

It seems to me that you have 2 start and 2 end times. If you use datediff between 00:18 and 07:00 and multiply by the rate, then between 07:00 and 09:30 and multiply by the new rate and add these 2 together you'll have your answer.

"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[SemperFiDownUnda]

ok I'm going to do something here that I normally don't do....post fully working code....but I'll explain it too

The main code of interest is the bolded text at the bottom of the post

it calculates the pay in to parts.
1st it calculates the pre 07:00 pay.

IIf(CDate(txtStart.Text) < dPayChange, _
             Abs(DateDiff("n", CDate(txtStart.Text), IIf(CDate(txtEnd.Text) < dPayChange, CDate(txtEnd.Text), dPayChange))) * CDbl(dRate1 / 60), _
             0)

if txtStart.text is after 07:00 this portion returns Zero
if it isn't it calculates the amount of time the worker has worked from txtStart to 07:00. Remember you have to account for a txtEnd time of before 07:00 so can't just do a datediff from txtStart to 07:00

The second part does other half of working out how much time after 07:00 has been worked and in a similar maner will calculate that portion.

IIf(CDate(txtEnd.Text) > dPayChange, _
         Abs(DateDiff("n", CDate(txtEnd.Text), IIf(CDate(txtStart.Text) < dPayChange, dPayChange, CDate(txtStart.Text)))) * CDbl(dRate2 / 60), _
         0)

The only thing this routine will not do is calculate a shift that starts before midnight and ends after midnight.

Private Sub Command1_Click()
  Dim dPayChange As Date
  dPayChange = TimeSerial(7, 0, 0)
  Dim dPay As Double
  Dim dRate1 As Double
  Dim dRate2 As Double
  'assign pay rates
  dRate1 = CDbl(txtRate1.Text)
  dRate2 = CDbl(txtRate2.Text)
[b]  dPay = IIf(CDate(txtStart.Text) < dPayChange, _
             Abs(DateDiff("n", CDate(txtStart.Text), IIf(CDate(txtEnd.Text) < dPayChange, CDate(txtEnd.Text), dPayChange))) * CDbl(dRate1 / 60), _
             0) + _
         IIf(CDate(txtEnd.Text) > dPayChange, _
         Abs(DateDiff("n", CDate(txtEnd.Text), IIf(CDate(txtStart.Text) < dPayChange, dPayChange, CDate(txtStart.Text)))) * CDbl(dRate2 / 60), _
         0)[/b]
  txtPay.Text = Format(dPay, "0.00")
End Sub

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first