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.
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
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.
=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ün sayı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.
=DATEVALUE("01/01/2024")
EDATE(Tarih; ay sayısı): İlgili tarihe belirtilen ay kadar ekleme yapar. Örneğin, 25 Mart'a 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ğine göre 28 veya 29 Şubat döndürür. Ancak 28 Şubat'a 1 ay eklendiğinde 31 Mart değil, 28 Mart döndürür. Bu fonksiyon, Oracle SQL'deki Add_months ve SQL Server'daki DateAdd fonksiyonlarına benzemektedir.
=EDATE(A1;2)
EOMONTH(Tarih; ay sayısı): İlgili tarihe belirtilen adet kadar ay eklendiğinde çıkan tarihin ay sonunu verir.
=EOMONTH(A1;2) //2 ay sonrasında 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.
Bu sorunu çözmek için bu tarihleri formatlamamız gerekir. Bu konuyu bu sayfadaki TEXT formülüyle yapıyoruz.
Ç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ü
Ç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 formü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)