Convert hour/minute to another predefined time mode.

Thanks.

All values are integers. Sorry, your method is not working.

Below is a short explann.

10 Hours : 52 Minutes >>>>> 27 : 10 (NN:VV)

10 hours * 2 1/2 = 25 N
52 min * 2 1/2 = 130 V (since 60 v = 1 N, 130 = 2 N : 10 V)
So, (25N + 2N) : 10V = 27N : 10V is Result.

The reverse calculation is.......

27 : 10 (NN:VV) >>>>> 10 Hours 52 Minutes

27*24 = 648 minutes = 10 Hours : 48 Minutes
10 VV = 4 minutes (60vv=24minutes)
So, 10 Hour : 48+4 mins = 10 Hr : 52 minutes is result.

Hope, this will be clear. To simply say, this uses the following...

24 Hour = 60 NN
1 hour = 2 1/2 NN
1 NN = 60vv

Like in clock, if it exceeds, 60 mins, another hour is counted, it works.

........

Originally Posted by DonkeyOte

Thanks for changing your title.

You don't specify if the entries in A1 & B1 are in time format or just general integer values... assuming the later... ie 10 is entered in A1 to represent 10 Hours and 30 in B1 to represent 30 minutes:

A2 for N: A1*(24/60)
B2 for V: B1/24

(this assumes Minutes in B1 would never exceed 60...)

To reverse do the opposite, ie / in A2 and * in B2

A2 for H: A1/(24/60)
B2 for M: B1*24

Does that help ?

Sorry, I made a mistake in the first post.

This is correct : 24 Min = 1 N = 60 V

>>Q: Where are getting 130 V from ?? how can this equate to > 2N ?<<

52 min = 2 N : 10 V
52*2 1/2 = 130 (52*2.5)
130/60 = 2.1666, Here 2 is NN and the balance .1666*60 = 10

Or

52/24 = 2.1666666 - Here 2 is NN & balance .1666*60 = 10


Hope, you will understand now. I think i corrected my mistakes, letus see.

Thanks.

Originally Posted by DonkeyOte

This is the part I don't get...



Previously you said 24 min = 1V ... in which case 52 min = 2.17 V ... or another way if you assume 1N = 2.5 hr then 52 min is equivalent to 0.87 N ...

Q: Where are getting 130 V from ?? how can this equate to > 2N ?

Re: to get the Hours to N (10 -> 25) - my mistake, the formula should of course be either one of:

=A1/(24/60)

or

=A1*(60/24)

Whichever is preferred.

I'm still confused about your V calc.

With 10Hr52Min, formula for A2 is fine.
B2 has little correction B2=60*MOD((b1/24),1) works fine.

BUT........... Just when i tried with someother timing, it goes wrong.

For instance :

17 Hr 38 Mins : Result should be 44-05 (NN-VV)
But here i get result of 43.5 - 35.

Here 43.5 is actually ......... (.50=30v)

43 N : 30 V & 35 V
Since 1 N = 60 V.......
30v + 35v = 65v = 1 N : 05 - this 1 N should be added to 43 N
so the result is 44-05

think, you will curse me

Originally Posted by DonkeyOte

OK so to confirm... looking at Hours (A1) & Mins (B1) to N (A2) & V (B2)

A1: 10
B1: 52

A2: =(A1/(24/60))+INT(B1/24)
B2: =60*(MOD(B1/24),1)

Greatttttttt........

I was breaking my head since a week. I was using vlookup to grab the result from two long tables (hour>NN:vv & Mins >NN:VV).

I think i can solve the viceversa formula (NN:VV to Hr:Mm), if unable, will reach you back.

Thanks a lot for the formula.

Oh... Thanks. This is a very elegant solution, i found. I am very poor in maths.
.......

Originally Posted by DonkeyOte

Your welcome though I suspect that one of the math guys here can provide a more efficient/elegant solution.

Re: working in reverse, perhaps:

Hours: =INT($A$1*(24/60))
Mins: =60*MOD($A$1*(24/60),1)+(($B$1/60)*24)

Where A1 holds N and B1 holds V

Hi,

Also,

Pl. provide the formula to find the seconds as well.

Hours: =INT($A$1*(24/60))
Mins: =60*MOD($A$1*(24/60),1)+(($B$1/60)*24)
Sec=?