A little help with the maths please (base 100 to 60)

[#1]

Hi

I have a field with numbers that ain´t in any timeformat. But the users knows that 0,15 are 15 min and 54,30 are 54 hours and 30 min.

But the calculation don´t end up correct beacuse qv use base 100 and the above field base 60. How can I sum the field correct?

So for example, 0,15 * 13 should give 3,15  (the user sees it as 3h and 15 min)

My guess is then that I don´t have to convert it into any timeformat but how should I do to so it count on base 60

0,15 / 60*100 *13 gives me the right answer but in the wrong formate beacuse the user whant to see 3,15 and not 3,25 and it fails when it goes over 0, xxx

The hard part I think is to get the sum of 1,15 + 1,15 to work out where the "1" acutally don´t have to be changed. I´m feeling a bit messy after som hours trying diffrent approch so hopefully anyone here can help me out.

[flipside]

Hi,

It's probably easier to convert the values to minutes, do the sum and convert back, as follows in the load script...

Hours:

LOAD *, (subfield(Duration,'.',1)*60) + subfield(Duration,'.',-1) as Minutes INLINE [

Duration

0.15

0.45

0.59

1.35

1.50];

Calculations:

Load Duration, Duration Minutes*(Rand()*250) as RandomSum resident Hours;

SET base60conv = Floor(Floor(RandomSum)/60) & ' hrs ' & num(floor(RandomSum - (Floor(RandomSum/60)*60)),'00') & ' mins';

In this example you could either put the conversion back to your desired format direct in the expression or as I have set as a variable and use =$(base60conv)

flipside

EDIT: My mistake, should multiply Minutes not Duration - fixed.

[#3]

Oh, thanks. But I´m not shure how I should use the variable in a expression?

[flipside]

The variable is just shorthand for the expression, so whatever the variable is set to will be calculated as if the variable value had been input as the expression instead.

[#5]

I ran into truble when the field are a integer, 200 becomes 12200 and not 12000. Can I somehow correct that?

[flipside]

Yeah, when the decimal part is missing it will add the integer part on again.  Quickest way is to format the number to force include a decimal part, something like  ...

LOAD *, (subfield(num(Duration,'0.00'),'.',1)*60) + subfield(num(Duration,'0.00'),'.',-1) as Minutes INLINE [

flipside

[#7]

Hi

Did not have time to respond yesterday but unfortunately that did´t work. The integer are god but the decimal numbers now converts to 0...

[flipside · Accepted solution]

Try the num format to 0,00 rather than 0.00 - difference is due to different locales.

EDIT: This might also be worth trying ...

LOAD *, (subfield(num(Duration,'#,##'),chr(44),1)*60) + subfield(num(Duration,'#,##'),chr(44),-1) as Minutes INLINE [

chr(44) =comma

[#9]

Ah, really nice of you to help me! the last suggestion worked brilliant, thanks