Sql Expression Help

[Delta729]

Help with the math please.



I'm trying to calculate the difference between start and end time, which works fine with:

Total Time: DateDiff("n",[tblTaskTimes]![StartTime],[tblTaskTimes]![EndTime])

However, this is for a database that calculates billable time in 10th of an hour. So, for example, 1 hours would be 1, 30 minutes would be .5, less than 6 minutes would be .1.

The format for this expression is set up to a general number, if that matters.

It's gotta be something in the formula like multiplying this by 24 (for 24 hours) and then dividing by something. Nothing I'm trying is working. Any ideas?

[June7]

If you want to round to the nearest tenth of an hour:

Say the DateDiff calc returns 1000 minutes, represented by x.

Int(x/60) + Round((x mod 60)/60,1)

Returns 16.7 hours

Be aware that Round() function in Access uses even/odd (banker's rounding) rule.

Round(3.45, 1) will return 3.4

Round(3.46, 1) will return 3.5

Round(3.35, 1) will return 3.4

However, if you want to always round up to the nearest tenth, that is a little more complicated. 7 minutes should round to .2 instead of .1?

[Delta729]

That's something I hadn't thought about. Basically:
1 second up to 5 minutes 59 seconds would be .1
6 minutes thru 11 minutes 59 Minutes would be .2
etc.

Basically, I wouldn't want to round, correct? (I'm lost and confused)?

[Delta729]

Okay, I've changed this so it will show total minutes on one expression:
Total Time: DateDiff("n",[tblTaskTimes]![StartTime],[tblTaskTimes]![EndTime])
Again, this works fine and give the total minutes.

And then calculate Billable or total minutes to roundup to nearest 10th of an hour with:
Billable: Int([Total Time]/60)+Round(([Total Time] Mod 60)/60,1)

But the billable isn't working correctly. How do I round this up?Any suggestions on how to fix this? I'm very much a beginner in Access, so please, reply for the layman.

[ssanfu]

First, I would advise not using spaces in object names.

Try this:

Billable: Int([TotalTime]/60)+Round(([TotalTime] Mod 60)/60+0.04,1)

NOTE: No spaces in "TotalTime"

[Delta729]

That's great and looks as though it's working, but that lost me. What does the +0.04 do? I kindof have the prior info from June7 figured out, but why add 0.04 in?

[ssanfu]

It forces the rounding to round up.

You said: "1 second up to 5 minutes 59 seconds would be .1"

If you have a start time of 8:30 and an end time of 8:32 what should the billable time be?
Actual time is 0.03333333333. Rounding would result in 0.

Now add 0.03333333333 +0.04
This results in .07333333333
Round that to 1 decimal results in .1

[Delta729]

Awesome, thanks again.