terima kasih banyak semuanya...
----- Original Message -----
From: Mr. Kid
To: [email protected]
Sent: Tuesday, February 05, 2013 4:22 PM
Subject: Re: [belajar-excel] nama hari berdasarkan tanggal
Coba gunakan fitur Conditional Formatting.
1. klik Cell B4 (misal cell ber formula di B4)
2. Buat conditional formatting pakai use formula :
xl2007 :: ribbon (menu) Home -> group Styles -> Conditional Formatting ->
New Rule -> pilih Use Formula (di xl2003 Formula Is)
-> tulis formula ini di textbox formula
=WeekDay( $b$1 - day( $b$1 ) + $a4 ) = 1
-> atur formatnya -> OK sampai kembali ke worksheet
3. Copy cell B4 yang juga berformula ke baris bawahnya
4. Atur area yang akan dikenai conditional formatting dengan :
>> klik cell B4
xl2007 :: ribbon Home -> group Styles -> Conditional Formatting -> Manage
Rules -> pilih Conditional formatting hasil pembuatan yang tadi
-> lihat kananya ada area yang dikenai Conditional Formatting
-> klik range selector (kotak kecil di area itu di sisi kanan)
-> pilih range -> Enter atau klik lagi range selector -> OK
sampai ke worksheet
Wassalam,
Kid.
On Tue, Feb 5, 2013 at 3:11 PM, MIKE <[email protected]> wrote:
1 lagi ketinggalan...
nah disitukan ada hari minggu kalo memunculkan otomatis misalkan fontnya
hari minggu menjadiwarna merah gimana pak?
tq
MIKE
----- Original Message -----
From: Mr. Kid
To: [email protected]
Sent: Tuesday, February 05, 2013 2:37 PM
Subject: Re: [belajar-excel] nama hari berdasarkan tanggal
Formula :
=Text(Date(Year($B$1);Month($B$1);Day(A4));"dddd")
menggunakan pemisah input dalam fungsi berupa karakter titik koma karena
setting regional (diatur melalui windows control panel) adalah Indonesia
Pada komputer dengan regional setting English, ganti karakter titik koma
dengan karakter koma menjadi :
=Text(Date(Year($B$1),Month($B$1),Day(A4)),"dddd")
Pemberian format "dddd" pada data bertipe datetime akan menghasilkan nama
hari sesuai regional setting komputer yang digunakan.
Coba formula :
=Text( $b$1 - day( $b$1 ) + a4 , "[$-421]dddd" )
>> hasil akan selalu berupa nama hari dalam teks Indonesia karena format
locale berbunyi [$-421]
Jika akan selalu dibuat menjadi huruf kapital, gunakan fungsi Upper,
seperti :
=Upper( Text( $b$1 - day( $b$1 ) + a4 , "[$-421]dddd" ) )
Jika akan membuat tipe data hasil tetap sebagai datetime tapi tampak
dimata sebagai teks nama hari, coba formula :
=$b$1 - day( $b$1 ) + a4
kemudian klik kanan -> format cells -> tab Number -> pilih Custom ->
tulis ditextbox [$-421]dddd -> tekan OK
sayangnya, ndak bisa jadi huruf kapital semua kecuali cell font dipilih
yang berupa font huruf kapital semua.
Wassalam,
Kid.
On Tue, Feb 5, 2013 at 1:20 PM, Jan Raisin <[email protected]>
wrote:
Coba formula berikut:
=Text(Date(Year($B$1);Month($B$1);Day(A4));"dddd")
Best Regards,
-Miss Jan Raisin-
2013/2/5 MIKE <[email protected]>
master excel....
Mohon bantuannya saya mau di kolom keterangan bisa muncul otomatis
nama2 hari (Minggu sd sabtu) jika kita memasukan data tanggal (angka 1.2.3.4
dst)
disini contoh bulan februari 2013 sebagai helpernya
terima kasih
MIKE
BULAN : Feb-13
TANGGAL KETERANGAN
1 JUMAT
2 SABTU
3 MINGGU
4 SENIN
5 SELASA
6 RABU
7 KAMIS
8 JUMAT
9 SABTU
10 MINGGU
11 SENIN
12 SELASA
13 RABU
14 KAMIS
15 JUMAT
16 SABTU
17 MINGGU
PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.
PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.
PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER:
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager. This message contains confidential information
and is intended only for the individual named. If you are not the
named addressee you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your
system. If you are not the intended recipient you are notified that
disclosing, copying, distributing or taking any action in reliance on
the contents of this information is strictly prohibited.
