|
| 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 |
|
#1
June 9th, 2005, 02:03 AM
|
|||
|
|||
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
June 9th, 2005, 02:28 AM
|
|||
|
|||
|
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 atyahoodotcom ---- "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
June 9th, 2005, 02:35 AM
|
|||
|
|||
|
ugh, sorry, scratch that suggestion ..
mis-read your post -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ----
|
|
#4
June 9th, 2005, 02:38 AM
|
|||
|
|||
|
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 atyahoodotcom ---- "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
June 9th, 2005, 02:44 AM
|
|||
|
|||
|
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
June 9th, 2005, 02:46 AM
|
|||
|
|||
|
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 atyahoodotcom ---- "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
June 9th, 2005, 02:53 AM
|
|||
|
|||
|
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 atyahoodotcom ----
|
|
#8
June 9th, 2005, 02:56 AM
|
|||
|
|||
|
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 atyahoodotcom ---- "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
June 9th, 2005, 03:10 AM
|
|||
|
|||
|
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
June 9th, 2005, 03:18 AM
|
|||
|
|||
|
"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
|
| 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
