Problems with Calculated time fields.. Any Help please

[Whack]

I'm using MS ACCESS 2021 on a WINDOWS 10 machine for the below conversation:



I've a database where a service is provided by time. So there's a StartTime and an EndTime field and they are in a date / time format and the time looks like HH:MM.

To compute the time spent I use a calculated form of [EndTime]-[StartTime] which produces the TotalTiemSpent in the form of total HH:MM

Then to produce the number of minutes spent on the service I use another calculated formula and multiply [TotalTimeSpent] * 1440 and this creates the value TotalMinutes spent on the service.

Now, I need to convert this to the format of hours so taking [TotalTimeSpent] / 60 produces TotalServiceTime which is number of hours in a decimal format. in the form of H.H Hours and 1/10's of hours

Here's the kickers..

1.

If I specify the result time as "Singe" and the "Decimal PLaces" value as 1, AND the resulting value of [TotalTimeSpent] works out to be some multiple of 1/10 of a hour, (00, 06, 12, 18, 24, 30, 36, 42, 48, 54) the decimal works out to be .0, , .1, .2, .3, .4, .5, .6, .7, .8, or .9

But in all other cases the decimal field is not honored.

Since I don't want to charge for anything between the full decimals, I'd like to round the values from 0.01 - 0.09 to .00, 0.11 - 0.19 to .10, 0.02 - 0.29 to .20 and so on... rather ACCESS produces something like: 1.666667E-02

2.

In Excel I can use a formula like:

=ROUNDDOWN(MROUND(PRODUCT(F7-E7,24),"0:06"),1)

where F7 - TimeEnd and E7 is TimeStart

But I can't seem to sort out how to cause this functionality to calculate in ACCESS...

I'd prefer to have this calculated and stored in the database as I suspect when working on millions of records for a report would tend to cause the machine to slow down in either a query or a report when having to calculate each included record (and yes, there are going to be millions - hundreds of millions of records
for each query / report)

Is there a bug in how ACCESS computes the decimal function in the case I'm interested?

Is there a way to cause this to compute the way I'd like it to?

I'm in the throws of learning ACCESS.. so any help / guidance will be greatly appreciated..

Thanks in Advance
---Whack

[mike60smart]

Hi
It is recommended that you should not use calculated fields in a table.

Calculations can be created in Unbound Controls in Forms or as a field in a Query.

See the attached example.

[CJ_London]

Think you are calculating this the wrong way round, get your rounded minutes then convert to hours/minutes.

to be clear you want to round down to the nearest 6 minutes?

in a date time field the time is stored as a decimal representing the time in seconds divided by 86400 (the number of seconds in a day)

so six minutes is 360 seconds so would have a value of 0.004166666666667

divide this into your calculated time will give you the number of 6 minutes - use the int function to strip of any decimal

now divide by 10 to get the number of hours, the remainder will be the number of 6 minutes

[ranman256]

use DateDiff("n", startTime, endTime) to convert elapsed time into minutes,
then you can sum the minutes

[Whack]

@mike60smart... Thank you... yes, I'm aware having a query or report would be the normal wayt do do this... I'm more than concerned that having literally millions of these records to process would cause a machine to bog down during the query / report processing phase... hence why I would prefer to have these values pre-built in the actual database record...

@CJ_Londan, Thank you.. I'm not sure I quite understand what you're suggesting I do.. I've tried several iterations using your methodology and it didn't quite work out... I'm stumped...Still trying to sort it out..

@ranman256, thank you... I don't seem to understand that one yet.. I'll have to find some documentation on datediff function

In the meantime.. here's what I've got ... And seriously, thank you for everyone's help... I'm just not experienced in either Excel or Access... I'm still learning... and Excel just hasn't got the muscle to do what I'm going to need to do...

[CJ_London]

I'm not sure I quite understand what you're suggesting I do.

I was on my phone but here is some examples written in the immediate window

?cdate(int(cdate("23:45:16") / (360/86400)) * (360/86400))
23:42:00

?cdate(int(cdate("11:16:00") / (360/86400)) * (360/86400))
11:12:00

rounding down the time to the nearest 6 minutes


the time in quotes is the equivalent of endtime-starttime

so a further example using a subtraction

?cdate(int((cdate("23:45:16")-cdate("11:16:00")) / (360/86400)) * (360/86400))
12:24:00


Edit:

I'm more than concerned that having literally millions of these records to process would cause a machine to bog down during the query

I doubt that would be the case - Nobody 'processes' millions of records in one go. They may well fetch a few from a dataset consisting of millions of records, but unless you are using criteria in your query based on this calculated value it won't have any real impact. And if you are using this as a criteria, make sure it is indexed otherwise you will be doing a sequential search which in itself will have a much more significant impact on performance. Are you really going to be searching for millions of records where the round down time =1:18:00 or between a range of times?

Also be aware that if end time is the next day, unless your value include a date as well as time (and just formatted to show only the time part), your calculation will be up the swanny

[Whack]

Thank you... it's still not doing what I really want... I'll play with it a bit and see what I can do... I truly appreciate everyones help... it's a learning curve for me... and I just need to get my feet on the ground...

Thanks again..
---Whack

[Welshgasman]

Here is something I used to use in my last place of work.
As long as the minutes did not come to any quarter, then you got the nearest tenth of an hour.

Sub TestCalcTime()
Dim dtstart As Date, dtend As Date
dtend = #5:40:00 PM#
dtstart = #9:00:00 AM#
MsgBox CalcTime(dtstart, dtend, 30)


End Sub

Public Const intInterval = 6 ' Rounding for hours

Public Function CalcTime(dtstart As Date, dtend As Date, Optional intDeduction As Integer)
Dim intMinutes As Long, intMod As Integer
CalcTime = DateDiff("n", dtstart, dtend) - intDeduction


' Round to 6 mins if not a 15 min interval
intMod = CalcTime Mod 60
If intMod <> 15 And intMod <> 30 And intMod <> 45 Then
    CalcTime = Round(CalcTime / intInterval, 0) * intInterval
End If


End Function

[CJ_London]

it's still not doing what I really want

if you want hours.10th of an an hour, a simple change to the formula

?int(cdate("23:45:16") / (360/86400)) * (360/86400)*24
23.7

?int((cdate("23:45:16")-cdate("11:16:00")) / (360/86400)) * (360/86400) * 24
12.4



if that is not what you want, you need to be much clearer