Archive

Posts Tagged ‘Excel 2010’

Formula Excel untuk menampilkan format tanggal/waktu (date/time)


excel format waktu

beberapa waktu yang lalu (udah agak lama banget sih, sampe lupa udah berapa lama postingan ini nangkring di draft) 😀 saya pernah diminta tolong untuk menampilkan format tanggal (date) pada satu cell yang juga akan di gabung (concatenate) dengan text lain misalnya “tanggal …”. Tanggal yang dimaksudkan ini sudah tersedia dari cell lain dengan format date. Misalnya pada cell A1 berisi “4/3/2015”, dan kita menginginkan pada cell A2 berisi “tanggal 4/3/2015” atau “tanggal 3 April 2015”.

Pada excel sendiri format date sebenarnya merupakan bilangan yang memiliki nilai (value) numeric biasa yang apabila ditampilkan dengan format general atau text hanya akan menampilkan susunan beberapa angka yang tidak berarti bagi pembaca. Misalnya tanggal “3 April 2015” (dengan format date) apabila cell format-nya diganti general maka akan menampilkan 42097.

Read more…

Categories: Office Tags: , ,

memecah data excel dari satu kolom menjadi beberapa kolom (split cell data by value)

August 28, 2014 1 comment

kebetulan tadi ada yang bertanya mengenai excel lagi, yaitu gimana caranya memisahkan data excel ke dalam beberapa kolom berdasarkan kode pemisah tertentu, misalnya tanda koma “,”. sebenarnya ini bukan hal yang sangat luar biasa, tapi mungkin sering kita jumpai terutama bila kita bekerja dengan excel yang datanya diimport dari sumber teks lain seperti hasil extract pdf, hasil copy word, saat bekerja dengan csv, atau beberapa kemungkinan lainnya. solusinyapun sangat sederhana, dan saking sederhananya sampai-sampai saya males untuk sekedar mengingatnya.. 😀 😀 yah walaupun keahlian per-microsoft office-an saya hanya sebatas muridnya mbak gugel, tapi apa salahnya dicatet buat keperluan saya esok hari.. 🙂

kondisi awalnya seperti ini: misalnya ada cell di excel yang isinya “irsan, 29 tahun, ganteng” dan kemudian ingin di-split menjadi tiga kolom yaitu “irsan”, “29 tahun”, dan “ganteng” (jumlah data dan kolom menyesuaikan kebutuhan).

berikut langkah-langkahnya:

  1. select (blok) semua data yang ingin displit
  2. klik tab “data”
  3. klik icon “text to column”
  4. pilih “delimited” kemudian klik “next”
  5. uncheck “tab” dan check “comma”, lalu klik “next” (delimiter lain bisa menyesuaikan kebutuhan)
  6. ubah rincian “destination” ke lokasi cell yang dinginkan, kemudian klik “finish”

sudah gitu aja sih..

Categories: Office Tags: , ,

membuat comments dinamis sesuai konten cell pada microsoft excel

July 15, 2014 Leave a comment

excel comments

comments pada excel sifatnya mirip seperti tooltip teks yang muncul saat kita menggerakkan kursor mouse ke cell tertentu yang sudah disisipi comment. biasanya comments ini digunakan saat kita ingin menambahkan suatu keterangan tambahan pada suatu cell *saya sendiri jarang (banget) menggunakan comment pada excel* :p

namun ada kalanya kita mau membuat comment yg muncul itu sifatnya dinamis sesuai dengan konten dari cell yang berubah-ubah isinya. nah kan repot kalo kit harus mengedit satu-persatu sekian banyak cell comments yang kontennya berubah.

oke, begini perumpamaan kebutuhannya, nanti solusinya bisa disesuaikan dengan kebutuhan lainnya, ini cuma misalnya aja 🙂

misalnya pada kolom A kita mau isikan kode “1” atau “2”. kemudian dengan berdasarkan konten tadi kita pengen comment yang muncul adalah “satu” atau “dua”. jadi pada saat mouse kita gerakkan ke kolom A yang konten cell-nya “1” maka akan muncul comment seperti tooltip teks yang berisi “satu”. atau akan muncul comment “dua” bila konten cell pada kolom A bernilai “2”.

bagi yang sudah biasa menggunakan vba pada produk office microsoft tentunya ga asing dengan hal-hal sebangsa ini. tapi bagi yg ga biasa seperti saya :p tentunya bingung mesti memulai dari mana, karena comment ga bisa digenerate dari formula bar *atau mungkin cuma saya aja yg ga tau caranya* 😀 😀

untuk mengaktifkan layar vba pada excel 2003 bisa dengan cara klik menu “Tools>Macro>Visual Basic Editor”

atau bagi yang menggunakan excel 2007 ke atas, buka “excel option” (dari icon office pada excel 2007 atau dari tab file pada excel 2010). kemudian pilih “customize the ribbon”. pada pilihan “customize the ribbon” di bagian kanan dialog, pilih “main tab”. beri tanda check pada “developer”. klik OK. sekarang kembali ke workbook excel dan klik tab developer, kemudian klik “visual basic”.

nah sekarang setelah layar kode vba muncul, kita coba copy paste kode di bawah ini:

Public Com1
Public Com2

Private Sub Worksheet_Change(ByVal Target As Range)

Com1 = “satu”
Com2 = “dua”
On Error Resume Next
If Target.Column = 1 Then
If Target.Value = 1 Then
Target.AddComment
Target.Comment.Text Com1
ElseIf Target.Value = 2 Then
Target.AddComment
Target.Comment.Text Com2
Else
Target.ClearComments
End If

End If
End Sub

kode vba

hmm… sepertinya mudah.. 😀

trus gimana kalau kita pengen comment pada cell di kolom A tadi disesuaikan dengan konten yang ada di kolom B?

yah, tinggal diganti aja sedikit parameter yang ada di isian comment “Com1” atau “Com2” misalnya jadi seperti ini:

 Com2 = “dua” & Range(“$B$” & Target.Row).Value

untuk membuat kode macro ini tetap bekerja setelah file ditutup, jangan lupa menyimpannya dalam format “Excel Macro-Enabled Workbook (*.xlsm)”.

save comment

sekian 🙂

 

Categories: Office Tags: ,

formula untuk membandingkan format waktu dalam excel (compare time in excel)

April 25, 2013 32 comments

excel formula

kali ini saya akan coba membahas sedikit mengenai fungsi pada excel, khususnya untuk membandingkan format waktu (date/time). saya tertarik menulis masalah ini karena saat ini saya sedang mengerjakan rekap absen, dan iseng-iseng saya ingin mencoba membuat formula untuk mengecek keterlambatan setiap pegawai dengan data dari mesin absen dengan menggunakan fingerprint.

awalnya saya harus memeriksa apakah status pointnya adalah kedatangan (check in) atau kepulangan (check out), lalu baru membandingkan dengan batas waktu kedatangan atau kepulangan, dan menentukan flag untuk terlambat atau pulang cepat.

baiklah, untuk mengecek kondisi status point bisa dilihat dari salah satu kolom yang tersedia apakah terisi “Check In” yang berarti kedatangan, atau berisi “Check Out” yang berarti kepulangan. rasanya formula untuk permasalahan ini cukup sederhana yaitu dengan menggunakan fungsi IF yang bentuk dasarnya adalah IF(KONDISI; VALUE/FUNGSI BILA BENAR; VALUE/FUNGSI BILA SALAH). contoh penggunaannya adalah sebagai berikut:

=IF(F15=”Check In”;”fungsi kedatangan”; “selain fungsi kedatangan”)

nah sekarang kita beralih ke permasalahan utama, yaitu membandingkan waktu. pertama yang harus kita lakukan adalah dengan mengkonversi teks dari cell tertentu menjadi format waktu (time), cell ini memiliki teks misalnya “07:20” tanpa tanda petik. teks ini (misalnya pada cell D5) bisa kita konversikan menjadi format waktu dengan fungsi TIME(JAM; MENIT; DETIK). fungsi ini dikombinasikan dengan fungsi LEFT(CELL; JUMLAH DIGIT) dan RIGHT(CELL; JUMLAH DIGIT) untuk mendapatkan angka jam dan menit dari suatu cell tertentu. gabungan dari fungsi ini kita gunakan sebagai berikut:

=TIME(LEFT(D15;2);RIGHT(D15;2);0) -> yang misalnya kita simpan di cell E15

yang pada contoh ini 07:20 akan dikonversi menjadi 07:20 AM dengan format waktu. bila konversi telah selesai kita lakukan maka kita bisa langsung membandingkan dengan waktu yang kita inginkan misalnya jam 07:30, dengan formula kondisi WAKTU1 > WAKTU2. cara menuliskannya adalah sebagai berikut:

=IF(E15>TIME(7;30;0);”status terlambat”;”status tidak terlambat”)

atau untuk memeriksa apakah pegawai tersebut pulang cepat (kurang dari jam 16:00), maka formulanya kita modifikasi menjadi:

=IF(E15<TIME(16;0;0);”status pulang cepat”;”status tidak pulang cepat”)

lalu bagaimana lagi? sebenarnya semua fungsi utamanya sudah kita buat,dan  bila memang ingin terlihat lebih simpel dengan tidak membuat begitu banyak kolom tambahan, maka fungsinya bisa kita gabungkan agar menjadi seperti ini:

=IF(F21=”Check In”;(IF((TIME(LEFT(D21;2);RIGHT(D21;2);0))>TIME(7;30;0);”status terlambat”;”status tidak terlambat”)); (IF((TIME(LEFT(D21;2);RIGHT(D21;2);0))<TIME(16;0;0);”status pulang cepat”;”status tidak pulang cepat”)))

bila dengan menggunakan formula diatas masih terdapat error, mungkin karena pengaturan separator untuk formula pada excel berbeda dengan yang saya gunakan, coba ganti karakter titik koma “;” menjadi koma “,”. selamat mencoba…..