Calculating with time and 'normal' numbers

[florens]

Hi,

I have 5 fields: Date1 (start date), Time1(start time), Date2 (end date, usually the same as Date1), Time2 (end time) and costhr(cost per hour).
I want a field on my report which contains my total cost (time * costhr) but I don't know how to do this. Does anybody know how?

Thanks in advance,

Florens

 

[MichaelRed]

IF (and ONLY IF) the dates are the same, then (end time - start time) * costhr is appropiate.

The other possabilities are quite a bit more complex, and depend on your business rules and work hours. For example, if the situation is simply a shift work process, where the actual work is not interrupted, then you just need to add the time values to the date values and perfom a similar calculation. BUT, if the work is interrupted (stop at quitting time of one day and finish on some other day) you need to calculate the working hours for each day from start date to end date, including hte partial hours of the start and end. Additional concerns may also apply, such as break time, meal time, ... again dependent on you business rules.

One additional item to consider would be the use of a single 'field' for the start and stop date/times. Ms. A. will use the same data type for the combiniation as each component so htere is actually a (small) saving to the storage of hte combination and it is generally easier to process the combined date/time than the seperate date & time fields.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[florens]

Thanks for your reply.
Since I'm getting my data out another database used by an application we're working with I can't put the data and time into one field.
The end date can differ from the start date but the work isn't interrupted in between so that makes it a lot easier.

I've tried to do (end time - start time) * costhr but I don't get a correct outcome. This probably comes from the fact that the date fields aren't really date fields (they're number fields and with format is looks like a date/time field). Do you have any clue how to solve it?

Thanks,

Florens

 

[MichaelRed]

OOps - "my Bad", the delta time is in the fraction of a day. Multiply be 24 to get hours, then it is o.k..

Illustrated:

Public Function basTimeRate2Chrg(EndTime As Date, _
                                    StartTime As Date, _
                                    MyRate As Currency) As Currency

    basTimeRate2Chrg = ((EndTime - StartTime) * 24) * MyRate



End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Hi,
Doesn't it all depend on whether any remainders are to be rounded up or down???
You can use the "Round" function in Access to do normal rounding based on a specific number of decimal places.
HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

Hmmmmmmm, I think using the currency data type takes care of the general rounding. If this uses "Lawyer math" for billing purposes, then the delta time would need to be adjusted sommewhere within the process, but not necessarily at this point. One might do the rounding in the database storage (here, it would be the source for the times). Other options depend on the overall app, and any 'adjustment' might be accomplished in a query which retrieved the times for the 'cost' calculation.

MY only intent was to explain / illustrate the math as requested (no error trap, no amplification, no ... residual responsability). Standard disclaimer and all that.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[florens]

Thanks for all your reply's, I've fixed my problem.
I've made a field on my report where the source is:
=IIf([Date1]=[Date2];(([Time2]-[Time1])*24)*[costhr];((24-([Time1]*24))+([Time2]*24))*[costhr])

Thanks again, you've been a great help

Florens