Oh... bagian : TRIM(SUBSTITUTE(RIGHT(B4,7),RIGHT(B4,2)," "&RIGHT(B4,2))) bisa dipakai replace menjadi : TRIM(RIGHT(REPLACE(B4,LEN(B4)-1,0," "),8))
Wassalam, Kid. 2013/3/6 Mr. Kid <[email protected]> > Oh iya, betul Pak Anton, bagian konversi waktunya masih kurang pas. Terima > kasih. > > Sepertinya bisa diubah jadi : (sebarang regional setting, sesuaikan deret > 3 karakter nama bulan sesuai cara penulisan data) > =DATE(MID(B4,7,5),FIND(LEFT(B4,3)," > JanFebMarAprMayJunJulAugSepOctNovDec")/3,MID(B4,5,2))+TRIM(SUBSTITUTE(RIGHT(B4,7),RIGHT(B4,2)," > "&RIGHT(B4,2))) > > Wassalam, > Kid. > > > 2013/3/6 summonery <[email protected]> > >> ** >> >> >> Hanya jika regional setting komputernya membaca nama bulan dalam bahasa >> inggris, misal regional settingnya English US, bisa dicoba formula berikut >> ini sebagai alternatif. >> =SUBSTITUTE(MID(B4,5,2)&LEFT(B4,3)&MID(B4,7,5)&" >> "&REPLACE(RIGHT(B4,7),FIND(":",RIGHT(B4,7))+3,0," "),"|","|")+0 >> >> *Mohon dicek kembali hasil konversi di cell C6, konversi 12:35 PM ke >> dalam sistem 24jam seharusnya menjadi 12:35 (bukan 0:35)* >> >> >semoga bermanfaat >> >> >> --- In [email protected], "Fahmi Baskara Gmail" >> <fahmi.baskara@...> wrote: >> > >> > Dear Para master Excel… >> >> > >> > Mohon bantuannya para master untuk mengubah format text menjadi format >> date seperti contoh di bawah ini. >> > >> > >> > Date (Text) >> > >> > Hasil Konversi >> > >> > >> > Jan 17 2013 3:59PM >> > >> > 1/17/13 15:59 >> > >> > >> > Feb 23 2013 2:51PM >> > >> > 2/23/13 14:51 >> > >> > >> > Mar 16 2012 12:35PM >> > >> > 3/16/12 0:35 >> > >> > >> > Apr 28 2012 8:28AM >> > >> > 4/28/12 8:28 >> > >> > >> > May 31 2012 10:04PM >> > >> > 5/31/12 22:04 >> > >> > >> > Jun 5 2012 7:38AM >> > >> > 6/5/12 7:38 >> > >> > >> > Jul 23 2012 8:47AM >> > >> > 7/23/12 8:47 >> > >> > >> > Aug 23 2012 8:13AM >> > >> > 8/23/12 8:13 >> > >> > >> > Sep 3 2012 9:46AM >> > >> > 9/3/12 9:46 >> > >> > >> > Oct 2 2012 3:46PM >> > >> > 10/2/12 15:46 >> > >> > >> > Nov 7 2012 9:47AM >> > >> > 11/7/12 9:47 >> > >> > >> > Dec 30 2012 9:56PM >> > >> > 12/30/12 21:56 >> > >> > >> > >> > Saya lampirkan juga file-nya >> > >> >> >> > >
