Decimal to 24 hour clock please.
 

Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

ugh, sorry, scratch that suggestion ..
mis-read your post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

This seems to do it ..

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

"Max" wrote:

Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

I've re-posted the revised formula in the other response,

Put in B1:

=IF(OR(A1={0,24}),0,(LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)
/(24*60))+0.5)

Format B1 as Time, Type:"13:30", and copy down

That should do it ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max!

Not working for me.

The formula is returning the decimal equivalents and when formatted as TIME
13:30 displays as TIME AM

1:43
9:52
3:17

If I add 12 hrs it works!

Biff

"Max" wrote in message
...
Assuming the decimals are in A1 down

Put in B1:

=IF(OR(A1={0,24}),0,LEFT(A1,SEARCH(".",A1)-1)/24+MID(A1,SEARCH(".",A1)+1,2)/
(24*60))

Format B1 as Time, Type:"13:30", and copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

Hi!

Maybe something like this:

=(INT(A1)+12&MOD(A1,INT(A1))*100)*1

Format as GENERAL

Biff

"Steved" wrote in message
...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Thankyou

"Steved" wrote...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100

Sorry, further testing reveals that the earlier revised formula is still not
robust enough. Try this 2nd revision below:

Assuming the decimals are in A1 down

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1 ,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steved" wrote in message
...
Hello Max from Steved

I need 6.30 to be 1830

Your Formula gives me 0.252083333

The reason for 1830 is that oracle understands 1830 is 6:30 pm

Hopefully you can work this out for me and thankyou.

2nd revision ..

Put instead in B1:

=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+1 ,2))=1,(TEXT(LEFT(A1,SEARC
H(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"
))+0.5,(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)
+1,2)/(24*60),"h:mm"))+0.5))

Format B1 as Time, Type:"13:30", and copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

urgh .. trash* it all, please.

See Harlan's offering ..

(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

urgh .. trash* all earlier suggestions, please.
(*Think my eyes are no longer able to distinguish reliably whether ":"
exists onscreen/print or not <bg)

See Harlan's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote...
Sorry, further testing reveals that the earlier revised formula is still
not
robust enough. Try this 2nd revision below:
....
=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1)+ 1,2))=1,
(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
/(24*60),"h:mm"))+0.5))
....

You're completely missing the point. You're fixated on returning Excel time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

"Harlan Grove" wrote
....
Your approach is so flawed it's breathtaking.

Yes, I know. Thanks, Harlan.
You probably just missed my post to trash it all ..

Even if the OP needed time values, you've still completely missed the
point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Point noted, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Your approach is so flawed it's breathtaking.

Nice one! <vbg

Biff

"Harlan Grove" wrote in message
...
"Max" wrote...
Sorry, further testing reveals that the earlier revised formula is still
not
robust enough. Try this 2nd revision below:
...
=IF(OR(A1={0,24}),0,IF(LEN(MID(A1,SEARCH(".",A1) +1,2))=1,
(TEXT(LEFT(A1,SEARCH(".",A1)-1)/24,"h:mm")+TEXT((MID(A1,
SEARCH(".",A1)+1,2)&"0")/(24*60),"h:mm"))+0.5,(TEXT(LEFT(A1,
SEARCH(".",A1)-1)/24,"h:mm")+TEXT(MID(A1,SEARCH(".",A1)+1,2)
/(24*60),"h:mm"))+0.5))
...

You're completely missing the point. You're fixated on returning Excel
time
values when the OP needs either integers or numeric strings that look like
integers. And he needs them as cell *VALUES*, not what's displayed.

Even if the OP needed time values, you've still completely missed the
point.
If hours are separated from minutes by a period, all that's needed to
convert to time in PM is

=--SUBSTITUTE(x+12,".",":")

Your approach is so flawed it's breathtaking.

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g)
Pl see responses given in the other branches.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Biff" wrote
....
Your approach is so flawed it's breathtaking.
Nice one! <vbg
I'll second that ! <vbg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Don't feel bad.

I once created an uber formula (that did work) and Daniel M. did the same
thing to me!

Biff

"Max" wrote in message
...
"Biff" wrote
...
Your approach is so flawed it's breathtaking.
Nice one! <vbg
I'll second that ! <vbg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Biff" wrote
Don't feel bad.

Not at all <g. But of course, I must be accountable for the earlier goofy,
flawed attempts to help the OP. Harlan was spot on with his suggestion to
the OP and his descripts/comments on my earlier efforts. All are accepted
in good spirit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks Harlan.

"Harlan Grove" wrote:

"Steved" wrote...
Hello from Steved

1.43 to 1343
9.52 to 2152
3.17 to 1517
I have the above in decimal please a formula to 24hour clock as above

ie 3.17 to become 1517 not 15.17

Far & away the shortest and fastest way would be

=(x+12)*100