Converting Time Calculation to Decimal

[Guest]

In a previous post this wonderful solution to calcuating the difference
between to Time entries for payroll calculation.

TimeIn TimeOut
8:00:00 AM 4:30:00 PM
Hours: Format([TimeIn]-1-[TimeOut],"Short Time") = 08:30

I now must multiply an employees hourly wage by the calculated time above.
example: $10.00 X 08:30
because its currency divided by a time datatype of course it doesnt
multiply, I've tried all types of Format functions to figure this out but am
stumped. Please how can I convert the time value to a corresponding
number/decimal value to calculate an employees pay.

thanks in advance.

 

[Duane Hookom]

First, don't format your hours. Apparently time may cross midnight. Try:

Pay: IIf([TimeIn]>[TimeOut],1-[TimeIn]+[TimeOut],[TimeOut]-[TimeIn]) * 24 *
[HourlyPayRate]

 

[onedaywhen]

In a previous post this wonderful solution to calcuating the difference
between to Time entries for payroll calculation.

TimeIn TimeOut
8:00:00 AM 4:30:00 PM
Hours: Format([TimeIn]-1-[TimeOut],"Short Time") = 08:30

I now must multiply an employees hourly wage by the calculated time above.
example: $10.00 X 08:30

Not so wonderful, IMO. Do you pay by the (completed) half hour?

SELECT #2006-10-06 08:00:00# AS start_date,
#2006-10-06 16:30:00# AS end_date,
DATEDIFF('n', start_date, end_date) AS workday_minutes_unrounded,
INT(workday_minutes_unrounded / 30) * 30 AS workday_minutes,
CCUR(10.00) AS hourly_rate,
CCUR(hourly_rate / 60 * workday_minutes) AS workday_earnings_gross

Jamie.

--

 

[Duane Hookom]

I expect (based on his posted expression) Bret's start and end times might
be on different dates and contain only time values with none longer than 24
hours. This would cause the DateDiff() to return a negative number for some
records.

I would also avoid using alias names in other calculations. Attempting to
sort or filter by workday_earnings_gross field/column would cause a prompt
for [Hourly_rate] and [workday_minutes].

--
Duane Hookom
MS Access MVP

In a previous post this wonderful solution to calcuating the difference
between to Time entries for payroll calculation.

TimeIn TimeOut
8:00:00 AM 4:30:00 PM
Hours: Format([TimeIn]-1-[TimeOut],"Short Time") = 08:30

I now must multiply an employees hourly wage by the calculated time
above.
example: $10.00 X 08:30

Not so wonderful, IMO. Do you pay by the (completed) half hour?

SELECT #2006-10-06 08:00:00# AS start_date,
#2006-10-06 16:30:00# AS end_date,
DATEDIFF('n', start_date, end_date) AS workday_minutes_unrounded,
INT(workday_minutes_unrounded / 30) * 30 AS workday_minutes,
CCUR(10.00) AS hourly_rate,
CCUR(hourly_rate / 60 * workday_minutes) AS workday_earnings_gross

Jamie.

 

[onedaywhen]

I expect (based on his posted expression) Bret's start and end times might
be on different dates and contain only time values with none longer than 24
hours.

I agree you could be right. I've presented an alternative taken on the
situation because I rather suspect the OP thinks they are storing only
the time elements and can't figure out why

(#1899-12-30 16:30:00# - #1899-12-30 08:00:00#) * CCUR(10.00)

doesn't return a dollar amount.

I would also avoid using alias names in other calculations.

I agree. My usage in such examples is merely a handy way of fabricating
data (without creating a table and inserting rows etc) to demonstrate
how it would behave in a query and to break down the proposed
calculation into steps. I certainly wouldn't do so in production code.
I should probably get into the habit of pointing out my usage. Sincere
thanks for picking up on the point.

FWIW the ability in Jet to refer to column correlation names
('aliases') from left to right is a violation of the SQL standard; all
the column correlation names in the SELECT clause are supposed to all
come in to existence at the same time.

Attempting to
sort or filter by workday_earnings_gross field/column would cause a prompt
for [Hourly_rate] and [workday_minutes].

Again, in doing so Jet violates the ANSI-92 standard; you are also
supposed to be able to refer to the column correlation names in the
ORDER BY clause. Really, the ORDER BY clause does not operate on a set
per se, rather it's part of a cursor (that's why the DECIMAL sort order
'bug' isn't significant but that's another story... <g>).

Can you sort a recordset using the column correlation names you used in
the query? Of course! Can you sort a recordset using the expression you
used to create the column in the query or using the table correlation
names employed? Of course not. An ORDER BY clause is supposed to work
the same way but Jet is not up to standards in this respect

Jamie.

--

 

[Duane Hookom]

Good comments and clarification.

--
Duane Hookom
MS Access MVP

I expect (based on his posted expression) Bret's start and end times
might
be on different dates and contain only time values with none longer than
24
hours.

I agree you could be right. I've presented an alternative taken on the
situation because I rather suspect the OP thinks they are storing only
the time elements and can't figure out why

(#1899-12-30 16:30:00# - #1899-12-30 08:00:00#) * CCUR(10.00)

doesn't return a dollar amount.

I would also avoid using alias names in other calculations.

I agree. My usage in such examples is merely a handy way of fabricating
data (without creating a table and inserting rows etc) to demonstrate
how it would behave in a query and to break down the proposed
calculation into steps. I certainly wouldn't do so in production code.
I should probably get into the habit of pointing out my usage. Sincere
thanks for picking up on the point.

FWIW the ability in Jet to refer to column correlation names
('aliases') from left to right is a violation of the SQL standard; all
the column correlation names in the SELECT clause are supposed to all
come in to existence at the same time.

Attempting to
sort or filter by workday_earnings_gross field/column would cause a
prompt
for [Hourly_rate] and [workday_minutes].

Again, in doing so Jet violates the ANSI-92 standard; you are also
supposed to be able to refer to the column correlation names in the
ORDER BY clause. Really, the ORDER BY clause does not operate on a set
per se, rather it's part of a cursor (that's why the DECIMAL sort order
'bug' isn't significant but that's another story... <g>).

Can you sort a recordset using the column correlation names you used in
the query? Of course! Can you sort a recordset using the expression you
used to create the column in the query or using the table correlation
names employed? Of course not. An ORDER BY clause is supposed to work
the same way but Jet is not up to standards in this respect

Jamie.