|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#11
June 9th, 2005, 03:21 AM
|
|||
|
|||
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 atyahoodotcom ---- "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
June 9th, 2005, 03:23 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
|
#13
June 9th, 2005, 03:33 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
|
#14
June 9th, 2005, 03:36 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
|
#15
June 9th, 2005, 03:43 AM
|
|||
|
|||
|
"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
June 9th, 2005, 03:54 AM
|
|||
|
|||
|
"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 atyahoodotcom ----
|
|
#17
June 9th, 2005, 03:58 AM
|
|||
|
|||
|
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
June 9th, 2005, 04:02 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
|
#19
June 9th, 2005, 04:09 AM
|
|||
|
|||
|
"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 atyahoodotcom ----
|
|
#20
June 9th, 2005, 04:24 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How to measure the distance between two lat and long points | A new kind of measurement | Running & Setting Up Queries | 6 | January 14th, 2005 04:45 PM |
| subtracting times using 24 hour clock | andrew pronto | Worksheet Functions | 6 | November 28th, 2004 02:26 AM |
| Time on outlook received messages is one hour behind clock on lap. | Time Challenged | General Discussion | 1 | September 16th, 2004 08:59 PM |
| y axis based on a 24 hour clock | diane | Charts and Charting | 4 | December 4th, 2003 05:02 PM |
Linear Mode
