calculate (rate) * (hours worked)

[hamish mather]

Please Can Someone help me

I have the folowing fields StartTime, EndTime, HourlyRate, in a table
HoursWorked: Format([StartTime]-1-[EndTime],"Short Time")

So Know I need HourlyRate*HoursWorked gives me error

StartTime 23:00 EndTime 10:00 = 12hours worked

PayAmount: Round(CCur(Nz(DateDiff("n",[StartTime],[EndTime])*[HourlyRate]/60,0)),2) this works but gives negative value

 

[student]

This is returning a negative value because 1000 is prior to 2300. Without dates this will always be negative. If, for example, start 2300 end 1200, the result would be 11 hours, not 13 as worked. I presume you meant 11 hours worked for 2300-1000? Or do you intend to add an hour to the calculation of time worked?

Not sure if there's a way around this without dates but I'm sure the guys will be able to tell you that.

 

[hamish mather]

how will i do that with date,,,it makes sense,have you got a formula

thanks

 

[student]

I'd convert the field to long date format (dd/mm/yy hh:nn:ss), but I suspect there will be a better way forward using the two fields together so prob best to wait for the experts on this one. Perhaps DateDiff("n",([StartDate] & " " & [StartTime]),([EndDate] & " " & [EndTime])) for your working minutes? I'll let the guys correct me on that one if(when!) it doesn't work.....

 

[vbaInet]

Will the hours ever be greater than 24 hours? If not just use the Abs() function to get rid of the -ve values.

 

[student]

But that would cause the same problem mentioned above, would it not?

"If, for example, start 2300 end 1200, the result would be 11 hours, not 13 as worked"

 

[vbaInet]

PayAmount: Round(CCur(Nz(DateDiff("n",[StartTime],[EndTime])*[HourlyRate]/60,0)),2) this works but gives negative value

I'm just going by what was said here. I wasn't taking anything else into consideration. It's in your capable hands.

 

[student]

It's in your capable hands.

Do'h!

I'd convert the field to long date format (dd/mm/yy hh:nn:ss), but I suspect there will be a better way forward using the two fields together so prob best to wait for the experts on this one. Perhaps DateDiff("n",([StartDate] & " " & [StartTime]),([EndDate] & " " & [EndTime])) for your working minutes? I'll let the guys correct me on that one if(when!) it doesn't work.....

Would this look like being the best way of doing it then? If so, I'm guessing a CDate or two in there would be required &/or safer from #ERROR results?

 

[vbaInet]

Yep, they will need the Date part too.

 

[student]

Woohoo! I'm getting better!

Cheers for confirmation. Nothing worse than giving out rubbish advice with total confidence!

 

[hamish mather]

I'd convert the field to long date format (dd/mm/yy hh:nn:ss), but I suspect there will be a better way forward using the two fields together so prob best to wait for the experts on this one. Perhaps DateDiff("n",([StartDate] & " " & [StartTime]),([EndDate] & " " & [EndTime])) for your working minutes? I'll let the guys correct me on that one if(when!) it doesn't work.....

I have to use date in that calculation....thanks for looking