Converting Time to Billable Units

[jdmartin011]

I have started my own consulting firm. I use access to record my time (BegTime and EndTime). I can net the 2 and get hours. How do I convert that to a number I can multiply by an hourly rate to get my total billings for that session?

[NeoPa]

How is it that the number of hours (which you say you have) is not what you want to multiply by the hourly rate to produce your billable value?

[zmbd]

Where and how are you doing the calculation?
It could be that your result looks unusual depending on the format of the field.

[jdmartin011]

I have two fields in a table to record BegTime and EndTime. In the table I have created a field BillableHours that is [EndTime]-[BegTime]. All three fields are stored as ShortTime Format. The result of 8:00-9:00 is 1:00 hours. When I multiply that result by $250 rate I get ($0.13).

[zmbd]

That's what I figured...
In MS OFFICE, All time is stored as a #####.####### type number that accounts for the elapsed time from 12/31/1889:00h00s00 so when you do a direct subtraction as you've appeared to do you return the fractional part. You would have seen this if the third field was formated as a general numeric instead of a date/time field.
Running late out the door... more in a few momements

[NeoPa]

Firstly, and I know I won't be the only one to point this out, you should certainly not be considering storing the result of your calculation (See Database Normalisation and Table Structures for reasons why not).

With that out of the way though, it seems you are treating hours within a date field as integral hours, when they are not. An hour in a date field is 1/24 of a day, which is the unit. Thankfully, this is very easy to handle by multiplying the time-differential (The result of your calculation) by 24.

[jdmartin011]

That is exactly what I have done. Appreciate the help. Take your time.

[jdmartin011]

Thank you NeoPa. I realize I need to change where I store results. For now just trying to figure out the calculation. I created a new field Billable Units. I multiplied BillableHrs by 24 it converted to a BillabeUnit of 1.0 which is good. When I multiply that result by 250 I get $3.

Nevermind. I think I figured it out. My rate is coming from a lookup table. That is where the problem is. I will figure that one out later.

When I input the rate directly in the table I get correct billable amount. Thank you to everyone for their help!!!

[zmbd]

Well, I guess my discourse the datediff() function isn't really needed; however, just to finish my thoughts in #5

In the query you need somthing like:
Billable: [chargerate]*(DateDiff("h",[est_startdate],[est_enddate]))

Usings an open recordset in vba you need something like:
zfltCharge = [chargerate] * (DateDiff("h", [est_startdate], [est_enddate]))