Dear BeExceller,
Excel mendapatkan data dari luar Excel itu biasa.
User meng-entry data langsung ke Excel cells pun biasa.
User meng-copy data dari luar Excel dan mem-Paste ke Excel pun biasa.
Yang tidak biasa adalah SANGAT SEDIKIT Excel user yang peduli dengan :
1. Regional Setting komputer setempat yang digunakannya
2. Memperhatikan format sumber data yang akan di-copy atau dimasukkan atau
diketikkan
Hal ini terjadi pada data bilangan (misalnya uang nilai Sales terkait
pemisah ribuan dan desimal) dan data tanggal.
Date Format berbeda dengan Date datatype.
Contoh date format :
27-05-2012 memiliki date format DD-MM-YYYY
2012-05-27 memiliki date format YYYY-MM-DD -> disebut ISO Date Format
(standar internasional)
05-27-2012 memiliki date format MM-DD-YYYY
*** ketiga nilai date di atas menggunakan delimiter dash '-', dan bisa jadi
sering ditemui menggunakan delimiter slash '/' seperti 27/05/2012 atau
5/27/2012
*** Nilai date diatas adalah 27 Mei 2012
Di Excel, nilai date tersebut di atas dengan format apapun bisa dijumpai
akan seperti gambar berikut :
Pada cells tanpa alignment seperti gambar
maka :
* Penampakan 1 : nilai tanggal bertipe TEXT karena nilai data tampak rapat
kiri seperti penulisan huruf umumnya.
* Penampakan 2 : nilai tanggal bertipe DATETIME karena nilai data rapat
kanan seperti penulisan bilangan umumnya.
*** Jadi, sudah jelas tampak bedanya Date Format dengan Date Datatype di
dalam Excel.
Pengambilan data dari luar Excel, terutama data tanggal perlu memperhatikan
:
1. Date Format sumbernya
2. Regional Setting komputer setempat
Berikut contoh beberapa format data tanggal dan hasil paste ke Excel cells :
*1. Sumber data memiliki date format DD/MM/YYYY*
> biasanya BeExceller menamakannya sebagai format Indonesian (padahal
tidak hanya Indonesia yang menggunakan format ini)
Keterangan :
- Sumber data berformat DD/MM/YYYY (kolom Aplikasi Data Sumber)
- Paste ke Excel di komputer dengan regional setting Indonesian yang
default date formatnya adalah DD/MM/YYYY [sama dengan format sumber data],
akan menghasilkan nilai Paste yang sesuai (benar apa adanya) dan bertipe
DATETIME [Lihat area kolom Indonesian DD/MM/YYYY terutama yang di-font BIRU]
- Paste ke Excel di komputer dengan regional setting dengan date format
SELAIN DD/MM/YYYY akan menghasilkan nilai bertipe datetime yang tidak
sesuai atau nilai bertipe text yang perlu proses lanjutan untuk
mengkonversinya menjadi bertipe datetime.
*2. Sumber data memiliki date format MM/DD/YYYY*
> biasanya BeExceller menamakannya sebagai format English
Keterangan :
- Hasil paste yang sesuai dan bertipe datetime hanya di komputer dengan
regional setting English juga [Lihat area kolom English (US) M/DD/YYYY
terutama yang di-font BIRU] karena memiliki date format yang sama dengan
sumber data.
*3. Sumber data memiliki date format YYYY-MM-DD (menggunakan standar ISO)*
Keterangan :
- Apapun regional setting komputer setempat, proses Paste akan menghasilkan
nilai yang sesuai dan bertipe DATETIME.
- Jadi tidak perlu repot memikirkan proses konversi apapun.
Jadi,
1. Kalau bisa (atau akan buat aplikasi tertentu di luar Excel), buatlah
gunakanlah date format YYYY-MM-DD (standar ISO) ketika akan meng-generate
Text File, atau sebelum melakukan Export to Excel.
2. Jika sumber bukan menggunakan standar ISO, ubah lebih dulu regional
setting komputer agar memiliki date format yang sama dengan sumber data.
Kemudian paste data tanggal, dan regional setting bisa dikembalikan seperti
semula lagi.
3. Jika tidak memungkinkan cara 2, maka sebelum copy sumber data, ubah
lebih dulu seluruh cells di worksheet lokasi paste menjadi berformat text
(format cells -> tab Number -> pilih Text). Setelah itu lakukan copy sumber
data, dan PASTE SPECIAL pilih Text di sheet tujuan.
4. Jika cara 3 tidak berhasil, maka konversi data dengan formula perlu
dilakukan.
Langkah proses dalam formula :
> Untuk kondisi hasil paste yang bertipe numerik menunjukkan nilai yang
salah
Contoh : data 01 APR 2012 di sumber data berbunyi 01/04/2012
[dd/mm/yyyy]
hasil paste menunjukkan 04 JAN 2012 [diformat
DD MMM YYYY]
a. cek, apakah cells data bertipe number atau tidak
= IF( isnumber( datanya ) , BETUL , SALAH )
b. jika hasil poin a adalah TRUE, maka isi bagian BETUL dengan
formula penyusun data tanggal yang benar dari data yang ada,
seperti menggunakan
fungsi DATE( tahunnya , bulannya , harinya )
tahunnya, bulannya, harinya bisa diperoleh dengan fungsi Year,
Month, Day
dan letakkan sesuai susunan yang benar.
Contoh : datanya menunjukkan 04 JAN 2012, harusnya 01 APR 2012
maka bulan_seharusnya = bulannya = Day( datanya )
hari_seharusnya = harinya = Month( datanya
)
tahun_seharusnya = tahunnya = Year(
datanya )
fungsi Date menjadi :
=Date( Year(datanya) , Day(datanya) , Month(datanya) )
c. jika hasil poin a adalah FALSE, maka data masih bertipe TEXT
dan nilai date bisa disusun dengan fungsi DATE( tahunnya ,
bulannya , harinya )
dengan tahunnya, bulannya, harinya diperoleh dengan fungsi LEFT,
MID, atau RIGHT
Contoh : datanya menunjukkan 17/09/2013, maka formula konversi :
=Date( right(datanya,4) , mid(datanya,4,2) ,
left(datanya,2) )
PERINGATAN dalam menggunakan fungsi VALUE atau konversi implisit dengan
mengalikan 1 :
> Jika data bertipe text memiliki format yang sama dengan regional setting
komputer, maka konversi dengan fungsi VALUE atau mengalikan data dengan 1
akan berjalan dengan baik.
> Jika data bertipe text memiliki format yang berbeda dengan regional
setting komputer, maka manfaatkan fungsi DATE (untuk data tanggal), atau
fungsi Substitute dan Value [atau dikali 1] (untuk data bilangan).
Contoh : regional setting komputer English format date M/DD/YYYY
datanya bertipe text berformat M/DD/YYYY seperti 3/05/2014
untuk 05 MAR 2014
=Value( datanya )
atau =1*datanya
bisa digunakan
Contoh : regional setting komputer English format date M/DD/YYYY
datanya tipe text format DD/MM/YYYY seperti 05/03/2014 untuk
05 MAR 2014
** karena format regional setting BERBEDA dengan format
datanya, maka
fungsi VALUE atau perkalian dengan 1 TIDAK bisa digunakan
** formula konversi menggunakan fungsi DATE dengan nilai
tahun, bulan, hari
untuk pengisi fungsi DATE diperoleh dengan MID, RIGHT,
LEFT
Demikianlah...
Regards,
Kid
