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 <[email protected]>
> *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.
>
>
>
