Converting a Calculated Time to Decimal

[lyonder2000]

I have a calculated "WorkHours" in my time application. In an effort to create a Productivity measure, I need to divide this time into a single integer.

(ie, WorkHours = 2:15, CompletedTasks = 20; CompletedTasks/WorkHours=20/2.25=8.89 audits per hour

 

[dhookom]

You should be storing time durations as numerics rather than as a date/time which are used to store a point in time. To get a number of hours from a time, multiply by 24. So
20/(24 * 2:15) = 8.89

Duane
Hook'D on Access
MS Access MVP

 

[lyonder2000]

I tried multiplying the "WorkHours" by 24 and my query returns a column full of "#Error"s. This is my last roadblock.

 

[dhookom]

What is the data type of your WorkHours field? What is the SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[lyonder2000]

WorkHours is a computed field but comoputed from a date/time field.

This query computes my time difference from a function:
SELECT qryLogData.RealDate, qryLogData.ActualTime, qryLogData.ID, qryLogData.emp_name, NextRec([ActualTime]) AS LogOutTime, Format([LogOutTime]-[actualtime],"Short Time") AS TimeDiff
FROM qryLogData
WHERE (((qryLogData.status)=1))
ORDER BY qryLogData.RealDate, qryLogData.ActualTime;

This query takes that info in prep for my report and formats:
SELECT id, emp_name, realdate, Format(Sum(CDate(TimeDiff)),"Short Time") AS WorkHours
FROM CalculateTimeDiff
GROUP BY id, emp_name, realdate;

I tried to convert the time by multiplying by 24:
SELECT (WorkHours*24) AS ConvTime
FROM FormatCalTimeDiff;

So now I'm trying to compute the division of the time for productivity:
SELECT [rprt audit].auditorid, [rprt audit].date, count([rprt audit].[trailer number]) AS NOA, ([NOA])/([FormatCalTimediff].[WorkHours]) AS PROD
FROM [rprt audit], FormatCalTimeDiff
GROUP BY [rprt audit].auditorid, [rprt audit].date;

 

[PHV]

The format function returns a string ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Don't format your dates/times until you want to display them. When you use Format([LogOutTime]-[actualtime],"Short Time") and Format(Sum(CDate(TimeDiff)),"Short Time") you are converting a perfectly good date/time value into a string that loses a ton of functionality. It might look pretty but is basically non-functional (like Jessica Simpson ;-)).

Try

SELECT qryLogData.RealDate, qryLogData.ActualTime, qryLogData.ID, qryLogData.emp_name, NextRec([ActualTime]) AS LogOutTime, [LogOutTime]-[actualtime] AS TimeDiff
FROM qryLogData
WHERE (((qryLogData.status)=1))
ORDER BY qryLogData.RealDate, qryLogData.ActualTime;

SELECT id, emp_name, realdate, Sum(TimeDiff) AS WorkHours
FROM CalculateTimeDiff
GROUP BY id, emp_name, realdate;

SELECT (WorkHours*24) AS ConvTime
FROM FormatCalTimeDiff;

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

A consequence of doing it the way you are (instead of the way Duane is showing you) is that your calculations are probably wrong.

If you had
[tt]tblTestDates
ftime ttime Elapsed
7:00:00 AM 5:00:00 PM 10:00
7:00:00 AM 5:00:00 PM 10:00
7:00:00 AM 5:00:00 PM 10:00
7:00:00 AM 5:00:00 PM 10:00
[/tt]
It all appears correct

and now you run

SELECT Format(SUM(cDate(Elapsed)),"Short Time") as Telapsed
FROM tblTestDates

You get
[tt]Query Result
Telapsed
16:00
[/tt]
The correct total is 40 hours ... not 16. The reason this happens is that a datetime field cannot hold a value of more than 24 hours without rolling over to the next day and you are ignoring the date part of the field

 

[lyonder2000]

I was able to enter the first set of code and it returned everything and the TimeDiff field displayed as a decimal, but the second set of code gave me a data type mismatch error, So I could not even run the last query.

 

[dhookom]

I expect you might have Nulls in [LogOutTime] or [actualtime]. Are these fields both date/time fields and do they all have values?

Duane
Hook'D on Access
MS Access MVP

 

[lyonder2000]

Yes, they actually do have values. I have it set up to save and add the record when a command button is clicked. In the event procedure of that button, it records the time

rst!ActualTime = Time()

that the button is pressed which is the person's login or logout time.

 

[dhookom]

However there are probably records that are not complete. Does the following return any records?

SELECT 
FROM qryLogData
WHERE qryLogData.status=1 AND ([LogOutTime] Is Null OR [actualtime] Is Null);

Duane
Hook'D on Access
MS Access MVP

 

[lyonder2000]

This returned no records

 

[jamaarneen]

Hi lyonder2000,

What I think is, that the problem might be, that date values are stored as a decimal, and the integer is for days, and the points for a fraction of a day.
so if you want to get the different between few hours, you will always get a fraction (a decimal with 0.###).
aND then you should calculate it to real hour value.

Here is a link , where you can see some functions aNd good advise, it should probably help you to get your result.

hope it will help
Ja

 

[jamaarneen]

Hi lyonder2000 again,

I'm am curious to know, if and how you solved your problem,

Thanks, Ja

 

[lyonder2000]

I think I've found that a procedure I created is formatting my time and therefore making it a string so multiplying times the result returns an ERROR. I am working on adjusting my procedure.