Number Ratios?

[Noob McKnownowt]

Hey guys, me again.

quick question.

I have a spreadsheet dealing with times, but they are represented in a
strange way. i.e.

If someone inputs 17:30 (5:30pm (half way between 5 and 6 pm)) the would
type it as:

17.30

Now i need this number (17.30) represented as a 100 based number so it
becomes:

17.50 (half way between 17 and 18)

An increase of 0.20, easy, but when the number can change to say 17.15, the
number then needs to be 17.25 (quarter past) this is an increase of 0.10.

So what i am asking is, does anyone know how to change numbers based on 60
(time) to number based on 100 via a calculation that can be applied to any
number in the 0 - 60 range resulting in a number from the 0 - 100 range?,
with the correct result being output.

I dont kow if this can be done using a ratio, or some strange VB function i
dont know about, but any help would be awesome, if you dont know what i am
rambling about then please feel free to ask a question.

 

[Mike H]

Hi,

You posted un programming but sound llike you want a worksheet solution. To
display a time as decimal try this

=A1*1440/60

With your time in A1 and formatted as general

Mike

 

[SeanC UK]

Hi,

Assuming the time (in normal units) is recognised as a time by Excel, then
you can use the formula:

=HOUR(A1)+(MINUTE(A1)/60)

Excel might try to format the result as a time, so either format the cell as
a number, or enclose the formula with VALUE():
=(VALUE(HOUR... .../60))

Sean.

 

[Noob McKnownowt]

Thanks for the replies guys, but i am not using formulas, i have written subs
to do this, so i am looking for code.

The only time it will be used is if the number contains a decimal place,
because it is only the "minutes" i need to try and convert to normal numbers:

And these number are being entered as general (with 2 decimal places)
numbers not times.

so 17.00 (5pm) can be left alone, but
17.15 needs to be changed to 17.25

i.e. A1 contains 8.45 and B1 contains 17.00

iNum1 = sheet1.range("A1").value
iNum2 = Sheet1.range("B1").value

iHours = num2 - num1

iHours will be 8.55 when the time equivilant result needs to be 8.15 meaning
my result needs to be 8.25

this can be done like:

iHours = iNum2 - iNum1 - 0.30

But if iNum1 is 8.30 then 0.20 needs to be taken away, i need a calculation
that no matter what number is entered the decimal place number will always be
represented by a 100 base number not 60.

i.e.

..15 = .25
..30 = .50
..45 = .75

And pretty much all numbers inbetween.

This is what is causing me the headache.

Hey guys, me again.

quick question.

I have a spreadsheet dealing with times, but they are represented in a
strange way. i.e.

If someone inputs 17:30 (5:30pm (half way between 5 and 6 pm)) the would
type it as:

Now i need this number (17.30) represented as a 100 based number so it
becomes:

17.50 (half way between 17 and 18)

An increase of 0.20, easy, but when the number can change to say 17.15, the
number then needs to be 17.25 (quarter past) this is an increase of 0.10.

 

[Mike H]

Maybe this

iNum1 = Sheet1.Range("A1").Value
iNum2 = Sheet1.Range("B1").Value
ihours = iNum2 - iNum1
tempnum = (ihours - Int(ihours)) / 100 * 60
ihours = Int(ihours) + tempnum

Mike

 

[Noob McKnownowt]

Hi Mike,

i havent tested this yet but if it works i will get a womb surgically
implanted and bare your children

you're a star pal.

The Noob.

 

[Mike H]

You may have to test this one as well because you somethingin your question
that threw me

iHours will be 8.55 when the time equivilant result needs to be 8.15 meaning
my result needs to be 8.25

Why does the decimal .55 = .15 = 0.25


Sub sonic()
iNum1 = Sheet1.Range("A1").Value
iNum2 = Sheet1.Range("B1").Value
ihours = iNum2 - iNum1
tempnum = ((ihours - Int(ihours)) / 60) * 100
ihours = Int(ihours) + tempnum
MsgBox ihours
End Sub

Mike

 

[Noob McKnownowt]

very sorry, i failed to tell you about another part of the sub.

say we have the same 2 numbers

iNum2 = 6.15
iNum1 = 18.00

the result of subtraction would be 11.85, further down in my code 0.50 is
subtracted to act as a 'Dinner time' period so half an hour needs to be taken
away

resulting in it being 11.35 (my mistake sorry)

and my result needs to be 11.25 (11 hours 15 minutes) represented as a 100
base number.

but sorry for the misinformation, but the code you have supplied has been a
tremendous help, i am currently playing about ith it a bit.

i will post the results

 

[Noob McKnownowt]

Its almost spot on, the only problem i am having now is that the results
returned by the double data type i am using is incosistend, is there any way
you know of to limit a doube to 2 decimal places?

The Noob

 

[Mike H]

post the code as ammended by yourself

Its almost spot on, the only problem i am having now is that the results
returned by the double data type i am using is incosistend, is there any way
you know of to limit a doube to 2 decimal places?

The Noob