Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
ugh, sorry, scratch that suggestion ..
mis-read your post -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 ---- |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
"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 |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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 ---- |
#13
|
|||
|
|||
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 ---- |
#14
|
|||
|
|||
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 ---- |
#15
|
|||
|
|||
"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. |
#16
|
|||
|
|||
"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 ---- |
#17
|
|||
|
|||
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. |
#18
|
|||
|
|||
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 ---- |
#19
|
|||
|
|||
"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 ---- |
#20
|
|||
|
|||
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 ---- |
#21
|
|||
|
|||
"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 ---- |
#22
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Production CLock | Excel Worksheet Functions | |||
Round Time with Conditions | Charts and Charting in Excel | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
decimal point override does not work | Excel Discussion (Misc queries) | |||
subtracting times using 24 hour clock | Excel Worksheet Functions |