04.08.2018 tarihinde VBA bölümüne ObjelerDünyası sayfası eklenmiştir

25.07.2018 tarihinde VBA bölümüne Outlook programlama sayfası eklenmiştir

13.07.2018 tarihinde Formlar-Kontroller sayfası eklenmiştir

25.05.2018 Hosting şirketi dğeiştirmekten kaynaklı bir hata nedeniyle Excelent add-ini indirirken hata alınmaktaydı. Bu hata düzeltilmiştir. İki ayrı download alternatifi sunulmuştur. Kurumunuzun BT politikalarının veya şahsi PC’nizdeki güvenlik ayarlarının izin vermesi durumunda yöntemlerden biriyle kurulum yapabilmelisiniz. Bi sorun olursa bana iletebilirseniz sevinirim.

Excel Formulas Menüsü(Fonksiyonlar) 3

Tarihsel Formüller

Tarihsel formüller, Excelde sık kullandığımız formüllerdendir. Bunları tek tek kullandığımız gibi başka fonksiyonlarla birarada da kullanımı oldukça yaygındır.

Öncelikle giriş mahiyetinde bir ön bilgi vermek isterim. Excel, tarihleri nümerik sayılar olarak tutar. Bunların tarih olarak gösterimi ise tamamen bir formatlama işidir. Mesela, bir hücrede 21.01.2017 tarihi varsa bu aslında 42756 sayısından başka bir şey değildir. Ancak Excel, girilen değerin tarihsel bir değer olup olmadığını anladığı için onu otomatik olarak formatlar ve 21.01.2017 olarak gösterir. Format Cell yapıp "General" tipini seçtiğinizde 42756'ya döndüğünü görürsünüz.

Şimdi genel olarak tarihsel formüllere bakacağız, sonrasında da çeşitli örnekler üzerinden sık ihtiyaç duyulan görevlerin nasıl yapıldığına bakacağız.

Genel Bakış

Formüller

NOW():Bugünün tarihini ve saat ve dakikasını verir. İstenirse Format Cell yapılarak saniye de gösterilebilir.

=HOUR(NOW()) //Şuanın saatini verir. MINUTE ile de dakikası elde edilir.

TODAY():Bugünün tarihini verir. Format Cell ile saat gösterilmeye çalışılırsa 00:00:00 gösterilir.

=IF(A2<TODAY();"Eski";"Yeni")

Dünün tarihini hesaplamak da zor değildir.

=TODAY()-1

Belirli bir tarihe kaç gün kaldığını hesaplamak da aynı derecede basittir.

=A2-TODAY()
//veya
=DAYS(A2;TODAY())

DAY(Tarih):İlgili tarihin ayın kaçıncı günü olduğunu verir.

=IF(DAY(TODAY())<=15;"Ayın ilk yarısı";"Ayın ikinci yarısı")

MONTH(Tarih):İlgili tarihin kaçıncı ayda olduğunu verir.

=MONTH(TODAY())

YEAR(Tarih):İlgili tarihin yılını verir. Aşağıdaki örnekte bugünün yılından "doğumgünü" isimli Named Range'in yılı çıkarılarak kişinin yaşı bulunmaktadır.

=YEAR(TODAY())-YEAR(dogumgünü)

Bu işlem aşağıdaki formülle de yapılabilir. Tek farkı, bunun küsuratlı bir değer vermesi ve gerektiğinde aşağı/yukarı yuvarlamaya imkan vermesidir. Mesela 38,9 yaşında çıkan birini 39 göstermek daha doğrudur.

=YEARFRAC(dogumgünü;TODAY())

WEEKDAY(Tarih;[Başlangıç]):İlgili tarihin haftanın kaçıncı günü olduğunu verir. İkinci parametre, haftanın ilk gününün ne alınması gerektiğini belirtir. Bilindiği gibi bazı ülkelerde haftabaşı Pazar iken bazılarında Pazartesidir. Başka seçenekler de var tabi ancak genelde 1 ve 2 kullanılacaktır. Default değer 1'dir, yani seçim yapmazsanız haftabaşı Pazar gibi alınır. Bizim ülkemiz için bu biraz kafa karıştırıcı olabilir.O yüzden bu fonksiyonu 2 ile kullanmanızı tavsiye ederim.

Mesela, bi kolondaki tarihlerden haftasonlarını işaretlemek veya filtrelemek istiyorsunuz, veya haftasonuysa şu, değilse bu şeklinde bir formül yazakcasınız

=IF(WEEKDAY(A2;2)>5;"Haftaiçi";"Haftasonu")

WORKDAYS(Başlangıç; kaçgün; [Tatiller]):Bir tarihe belirtilen adette işgünü ekler. İsterseniz bir hücre grubuna gireceğiniz tatil günleri ile bunları da eklenecek günlere dahil edebilirsiniz.

=WORKDAY(A1;7;B1:B3)

NETWORKDAYS(Başlangıç; bitiş; [Tatiller]):İki tarih arasındaki iş günü sayısını verir. İsterseniz bir hücre grubuna gireceğiniz tatil günleri ile bunları da hariç tutabilirsiniz.

=NETWORKDAYS(A1;A2;B1:B3) //B1:B3 arasında bayram tatilleri girilmiş

İki gün arasındaki toplam gün sayısı için ilgili tarihler birbirinden doğrudan çıkarılır ve 1 eklenir.

=A1-A2+1

Her iki yöntemde de başlangıç ve bitiş tarihleri günsayısına dahildir.

DATEVALUE(StringTarih):Metin formatında verilen tarihi Tarih formata çevirir. Böylece bu tarihi başka tarihlerle karşılaştırabilirsiniz. Ayrıca Özet tablo veya Grafik yapmak istediğinizde tarihler sıralı bir şekilde gelir ve tarih olarak kullanılır, aksi halde alfabetik sıraya göre gelir ve tarih özelliklerinden faydalanılamaz

 

EDATE(Tarih;aysayısı):İlgili tarihe belirtilen ay kadar ekleme yapar. Ör:25 Marta 2 ay eklenirse 25 Mayıs olur. Özel durum olarak 28 şubatı söyleyelim. 31 Ocak'a 1 ay eklenirse ilgili yılın artık yıl içerip içermediğien göre 28 veya 29 Şubat döndürür. Ancak 28 Şubata 1 ay eklendiğinde 31 Mart değil 28 Mart döndürür. Bu fonksyion bu bağlamda, Oracle SQL'deki Add_months ve SQL Server'daki DateAdd fonksiyonlarına benzemektedir.

=EDATE(A1;2)

EOMONTH(Tarih;aysayısı):İlgili tarihe beliritlen adet kadar ay eklendiğinde çıkan tarihin aysonunu verir.

=EOMONTH(A1;2) //2 ay sornasının ay sonunu verir

DATE(Yıl,Ay,Gün):Verilen Yıl, Ay ve Gün birleştirilerek ilgili tarih elde edilir..

=DATE(2017;1;21) //21.01.2017

Formatlama işlemleri

Bazen tarih formatı olan bir hücreyi bir metin formülü içinde kullanmak isteriz. Böyle bir durumda Excel otomatikman bu tarihin temel numerik değerini kullanır, ki bu da pek hoş olmaz. Ne demek istediğimi aşağıdaki örnekte görebilirsiniz

kısatanım

Bu sorunu çözmek için bu tarihleri formatlamamız gerekir. Bu konuyu bu sayfadaki TEXT formülüyle yapıyoruz.

Çeşitli Örnekler

Çeşitli günleri tespit etme

Sık kullanılan özel tarihleri aşağıdaki gibi özetleyebiliriz.

=EOMONTH(TODAY();0) //bu ayın aysonu
=EOMONTH(TODAY();-1)+1 //bu ayın başı
=TODAY()-DAY(TODAY())+1 //bu ayın başı(2)
=EOMONTH(TODAY();0)+1 //sonraki ayın başı
=EOMONTH(TODAY();1) //sonraki ayın sonu
=EOMONTH(TODAY();-2)+1 //geçen ayın başı
=EOMONTH(TODAY();-1) //geçen ayın sonu	
=TODAY()-WEEKDAY(TODAY();2)+1 //bir önceki Pazartesi: (Bugünden, bugünün gün numarasını çıkarıp 1 ekliyoruz)
=TODAY()+(8-WEEKDAY(TODAY();2)) //bir sonraki Pazartesi: (Bugüne, "7-bugünün gün numarası" farkını ekleyip 1 daha ekliyoruz) 
=WORKDAY(EOMONTH(date)+1,-1) //bu ayın son işgünü

Çeşitli seviyelerde artışlar yapma

Çoğu kez belirli bir tarihe belirli frekanslarda eklemeler yapmamız gerekecek, aşağıda bunları örnek olarak göstermek istedim.

Yıl eklemek için ayrıca aşağıdaki fomrül de yazılabilir. 5 yıl ekleyelim.

=DATE(YEAR(A1)+5;MONTH(A1);DAY(A1))

Tatil günlerini sayma

TatilRange isminde bir Named Range'imiz olduğunu düşünelim. Buraya belirli bir dönemdeki tüm tarihleri girmiş olalım. A1 ve A2 de sırayla başlangıç ve bitiş tarihleri bulunuyor olsun. Böyle bir durumda bu iki tarih arasındaki tatil günlerini aşağıdaki gibi hesaplarız.

=SUMPRODUCT((TatilRange>=A1)*(TatilRange<=A2))

Eğer buraya bir de haftasonlarını eklemek isterseniz formülümüzü şu şekilde güncelleyebiliriz.

=SUMPRODUCT((TatilRange>=A1)*(TatilRange<=A2))+A2-A1+1-NETWORKDAYS(A1;A2)

YORUMLAR