minutes calculation 2

[pcrmil]

Hi All,
I created a report,that report is about printed hrs and minutes totaled.

Minutes are storing
0.15
0.40
0.15
0.15
etc.

I used the formula on the report: group footer. Ex. if total minutes are 205 i used the formula below.
Sum([mts]/60*100)
to total. The minutes are totaled to 3hrs 42mts but it has to be 3hrs 25 mts. What I am doing wrong? Please explain how to correct the formula above to get correct answer. I will greatly appreciate if you could help me.

 

[JBinQLD]

It's because by multiplying the result by 100 you're converting the time to a decimal value - .42 of an hour is 25 minutes.

Try this:

=fix(sum(mts)/60) & " hrs " & sum(mts)-(fix(sum(mts)/60)*60) & " mts"

(To break down:
= 3 & " hrs " & 205 - ( 3 * 60 ) & " mts"

= 3 hrs & 25 mts )


JB

 

[Remou]

Dates and times are numbers, so another possibility:

[tt]=Sum([mts])\(60*24) & " Day(s) " & Format(Sum([mts])/60/24-(Sum([mts])\60*24),"h ""hours and"" m ""minutes""")[/tt]

 

[pcrmil]

Thanks to the both of you.

Hi JB

I used your syntax,but the mts is not printing correctly.

Also Remou

I tried your syntax also, it is printing zero.

Please let me know how I can print the mts correctly.

 

[JBinQLD]

To debug try breaking the line into smaller parts to see which is failing. Eg have three controls with

= fix(sum(mts)/60)
= sum(mts)
= (fix(sum(mts)/60)*60)

and see if they give values 3, 205 and 180 respectively. If they do start to joing them together or add the & " hrs " etc to them until you find the problem.

JB

 

[pcrmil]

Hi JB

Run the report based on the date parameter for 15 days .The Total calculated minutes are 135. When I apply the formula above, it is giving 2hrs 25 mts. Its suppose to be 2hrs15mts.

i dont know what i am doing wrong, please explain it.

 

[JBinQLD]

G'day,

To work out where it is going wrong, let me know what the three controls show as last post:

Eg have three controls with

= fix(sum(mts)/60)
= sum(mts)
= (fix(sum(mts)/60)*60)

which should give:
fix(135/60)=2
sum(mts)=135
fix(2*60)=120

Passing those figures through my original formula:

=fix(sum(mts)/60) & " hrs " & sum(mts)-(fix(sum(mts)/60)*60) & " mts"

=2 hrs 135-120

=2hrs 15

Can't see where it would fail. Please break down into the three components and check out the results incase I've screwed one of them up.

JB

 

[Remou]

I, too, am curious about a value. I tested my suggestion and it seemed ok. What is:

=Sum([mts])

?

 

[PHV]

What about this ?
=Sum([mts])\60 & "h" & (Sum([mts]) Mod 60)

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

 

[pcrmil]

Hi JBinQLD,

I broke down the syntax into three components and the results are below:

After I removed the fix on line below

sum([mts])/60)=0.02
sum([mts])=1.35
(fix(sum[mts]/60)*60)=00

PHV,

I tried your syntax also but it is not accepting, I am getting an error message.

thank you JBinQLD and PHV

 

[JBinQLD]

well, the biggest clue is of course the middle line - this shows that you're getting a value of 1.35 for sum(mts) whereas above you said it worked out to 135. The formula I provided assumed sum([mts]) equates to a round number of minutes, no decimal values

 

[pcrmil]

JBinQLD

Thnk you, users are entering mts 0.15. Is there any way to remove the decimals .

 

[JBinQLD]

make them enter an integer mate. Set the field type at table level or use a validation rule at form level.

You should probably look for further help on that as a new post as I'm pretty certain the Original thread has been resolved? The solutions offered for converting a decimal number of minutes to hours and mins seem pretty ample now, so you need to move onto filtering uer input

JB

 

[PHV]

I am getting an error message
Any chance you could post some more relevant info like, says, the error message ?

 

[pcrmil]

Hi PHV,

Thanks for your time. I didn't exactly remember the error message but part of the error message is a syntax error.

 

[JBinQLD]

Have you got your user input to add up to 135 yet?

jb

 

[TheAceMan1]

How are ya pcrmil . . .

Hmmmm ... try the following:

[blue]   = Int(Sum((([mts] - Int([mts])) * 100) + (Int([mts]) * 60)) / 60) & "hrs " & _
         Sum((([mts] - Int([mts])) * 100) + (Int([mts]) * 60)) Mod 60 & "mts"[/blue]

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[pcrmil]

TheAceMan1,

Thank you very much, it works very well, I just changed the peranthesis on the code.
It works in the report.

Can I enter the same code in the query also?

 

[pcrmil]

Hi,

Users are entering the hours and mts 8, 7.3, 6.3,9 etc.
Creating a report every day , and every month.

I created the cross tab query and I used the code below on the total column
= Int(Sum((([total] - Int([total])) * 100) + (Int([total]) * 60)) / 60) & "hrs " & _
Sum((([total] - Int([total])) * 100) + (Int([total]) * 60)) Mod 60 & "mts"
My cross tab fields are the deptname(group), the date(group), the empname(group), the total(value) but it is giving a error in the hrs and mts.
How can I get the hrs and mts on the cross tab query?

Please help me!

 

[klmorgan]

Just be thankful that we no longer use multiple base systems for money 12 pence to the shilling, 20 shillings to the pound and rods, poles and perches for measurement!

I always used decimal hours ie 6.27 hours and made sure that everyone recieving the info had an explaination that the decimal places were decimal parts of hours not minutes.
ie. .42 was .42 of an hour and if they wanted to get this to minutes they multiplied .42 by 60 to get 25 minutes.

When reporting totals I used to divide the toatl by 60 and used the integer as the hours and multiplied the decimal remainer by 60 to give minutes.

To try and get this in a form for a cross tab may be a task too far.

Regards

Keith