Decimal to 24 hour clock please.

[Guest]

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

 

[Max]

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

 

[Max]

ugh, sorry, scratch that suggestion ..
mis-read your post

 

[Max]

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

 

[Biff]

Hi!

If:

1.43 = 1343 PM

What would:

1.43 = ???? AM

And how do you distinguish one from the other?

Biff

 

[Guest]

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]

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

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 ..

 

[Biff]

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

 

[Biff]

Hi!

Maybe something like this:

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

Format as GENERAL

Biff

 

[Harlan Grove]

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

 

[Max]

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

 

[Max]

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

 

[Max]

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>)

 

[Max]

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 ..

 

[Harlan Grove]

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.

 

[Max]

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 !

 

[Biff]

Your approach is so flawed it's breathtaking.

Nice one! <vbg>

Biff

 

[Max]

Yes, I know, Biff.
It was my eyes and my mind (both need some repair <g>)
Pl see responses given in the other branches.

 

[Max]

Nice one! <vbg>

I'll second that ! <vbg>

 

[Biff]

Don't feel bad.

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

Biff