Tarihler ve Tarih İşlevleri - Excel İle Harikalar
Bu konuya başlamadan önce Excel tarihleri nasıl hesaplıyor ona kısa bir şekilde değinmekte fayda var. İlk olarak Excel’de tarih aslında bir rakam olarak tanımlanır. Bu rakam 1.1.1900 tarihinden itibaren geçen gün sayısıdır. Neden bu tarih kullanılmış? Bu yıl bir artık yıl olması nedeni ile bu yıldan itibaren her 4 yıl bir artık yıl olduğundan hesaplama kolaylığı sağlamış. Ek bir bilgi daha Mac Office bu tarih yerine 1.1.1904 tarihini başlangıç olarak alır. Bunun nedeni de ilk Mac bilgisayarların 1.1.1904 tarihinden önceki tarihleri algılayamamasıydı. Neden yazdım bu bilgileri? Tarih girdiğiniz hücrenin veri formatını daha sonradan rakama veya genele çevirdiğiniz zaman gördüğünüz alakasız rakamın anlamı bu.
Tarihler Excel’de nasıl yazılır? Normalde Gün, Ay, Yıl sıralaması ile istediğiniz tarihi araların .-/ gibi karakterleri kullanarak yazabilirsiniz. Örnek olarak 25/12/2016, 4-5-18 gibi ifadeler Excel tarafından tarih olarak algılanır ve hücre formatı da tarih olarak değiştirilir. Ancak burada bilgisayarın bölge ve dil ayarlarının etkin olduğunu bu nedenle Amerika veya Birleşik Krallık gibi ülke ayarları söz konusu ise tarih girildiğinde Ay-Gün-Yıl sıralamasının geçerli olduğunu unutmayın lütfen.
Tarih Girişi ve Format Örneği
Excel’de bir hücrede içinde bulunduğunuz günün tarihini göstermek istiyorsanız kullanacağınız formül BUGÜN formülüdür. Bu formül herhangi bir parametre istemez, yazılışı:
şeklindedir. İngilizce Excel kullanalar için TODAY() şeklinde yazılır. Bu formülü kullanırken dikkate edeceğiniz en önemli unsur dosya her açıldığına bu formülün kendini güncellemesidir. Yani içinde bulunduğunuz tarihi sabit bir değer olarak girmek istiyorsanız bu formül yerine SHIFT+CTRL+, tuşlarına basın.
Bu formül daha çok “yapılacaklar listesi” gibi belli bir bitiş tarihi olan görevlerin durumunu kontrol etmek için kullanılır. Misal bitiş tarihi ile bugün arasında pozitif fark var ise o görev için belirlenmiş süre aşılmış demektir ve duruma göre bir uyarı görüntülemek için “EĞER” formülü ile beraber kullanılabilir. Aşağıdaki resimde formülün nasıl yazıldığını ve sonucunu görebilirsiniz.
Bugün Formülü Yazılımı ve Sonuçları
Bugün ile benzer yazılıma ve özelliğe sahip bir formül olan ŞİMDİ ile BUGÜN’den farklı olarak saati de hücre içine yazmasıdır. Yine dosya her açıldığında veya sayfa yeniden hesaplatıldığında güncellenecektir. Bu nedenle sabit olması istenen tarih ve saat bilileri için kesinlikle kullanılmamalıdır. Yazılımı BUGÜN ile aynıdır:
Elde edilen sonuç 20/06/18 19:44:42 şeklide formülün girildiği tarih ve saat olacaktır. Genel olarak benim ihtiyaç duymadığım formüllerden bir tanesi. Ancak saate bağlı bazı görevlerin kontrolü için kullanılabilir.
Şimdi Formülü Yazılımı ve Sonuçları
GÜN, AY, YIL
Hücre içine girişmiş bir tarih değerinden sadece gün, ay, yıl gibi belli parçaları almak istersek kullanacağımız formüllerdir. Yazılışları:
Dikkat ederseniz Excel sizden buraya tarih formatına döndürülebilen bir numara veya böyle bir numara içeren hücre adresi girmenizi talep ediyor. Girmeniz gereken numara da başta anlattığım numaradır.
Girdiğiniz numara eğer tarih formatına uygun bir numara ise geri döndürülen değer sırası ile bu tarihin gün, ay, yıl değerleri olacaktır.
Örnek olarak 04/06/2018 tarihini bu formüllere yazarsak sırası ile 4-6 ve 2018 sayıları geri dönecektir.
Gün-Ay-Yıl Formülü Yazılımı ve Sonuçları
Bu formülün amacı verilen herhangi bir tarihin haftanın hangi gününe denk geldiğini bulmaktır. Formül sonucunda günü ifade eden 1 ile 7 arasında bir rakam döndürülür. Yazılımı:
İngilizce Excel için WEEKDAY olarak yazılır. Bu formülde ilke seri_no tarih veya tarih barındıran bir hücre adresi olmalıdır. Döndür_Tür ise haftanın başlangıç gününü belirtmek için kullanılır. Eğer buraya değer girilmezse veya 1 yazılırsa Pazar gününden başlayarak Cumartesi’ye kadar günler 1-7 rakamları ile gösterilir Pazar=1, Pazartesi=2,…, Cumartesi=7 gibi. Eğer 2 yazılır ise Pazartesi, Pazar günleri 1-7 arası numaralandırılır. 3 yazılır ise Pazartesi-Pazar arası günler 0-6 rakamları ile ifade edilir. 11 ile 17 arası rakamlar yazılır ise sırası ile başlangıç günü Pazartesi’den başlayarak Pazar’a kadar kaydırılır ve günler 1-7 arası rakamlar ile ifade edilir.
Burada dikkat edilmesi gereken husus döndürülen değerin bir rakam olduğudur. Eğer siz rakam yerine gün adını görmek isterseniz ek bir formüle daha ihtiyacınız olacaktır. Bunun için ELEMAN veya METNEÇEVİR formüllerini kullanabilirsiniz.
Elimizde başlangıç ve bitiş tarihi şeklinde bir tarih aralığı olduğunu düşünelim. Bu iki tarih arasındaki hafta sonu tatilleri hariç kaç mesai günü olduğunu bulmak isteyelim. Bu iş için kullanacağımız formül TAMİŞGÜNÜ formülüdür. ingilizce olarak NETWORKDAYS. Yazılımı:
=TAMİŞGÜNÜ(başlangıç_tarihi, bitiş_tarihi, [tatiller])
Bu formülde gördüğünüz üzere tatiller değeri isteğe bağlı olarak girilir. Girilmez ise sadece hafta sonları olan Cumartesi ve Pazar günleri hariç olarak başlangıç ve bitiş tarihi arasındaki toplam çalışma günü adedini rakam olarak geri döndürür. Tatiller kısmına ise iki tarih arasında tatil olarak belirlenmiş tüm tarihlerin yer aldığı bir aralık girilir ise bu tarihler de hariç olacak şekilde toplam iş günü elde edilir. Aşağıdaki resimde formülün nasıl çalıştığını gösterdim.
TamİşGünü Formülü yazılışı ve çalışması
Bu formül ile hafta sonu olan Cumartesi ve Pazar günleri çalışma günü olarak alınmaz. Eğer hafta sonu tatili günlerinde daha farklı değerler kullanmak istiyorsanız TAMİSGÜNÜ.ULUSL formülünü kullanmanız gerekmektedir.
TAMİŞGÜNÜ formülü başlangıç ve bitiş tarihlerini sayıma dahil eder. Yani başlangıç ve bitiş tarihlerini aynı girerseniz 1 sonucu geri döndürülür.
Son olarak bu formül sadece tarihler üzerinde çalışır. Eğer ŞİMDİ gibi saat içeren tarihler kullanırsanız, saat kısmı formül tarafından dikkate alınmaz.
TAMİŞGÜNÜ ile iki tarih arasındaki toplam iş gününü bulmayı gördük. Ancak farklı hafta sonu tatil günleri kullanan ülkeler ya da sadece Pazar günü tatil olarak çalışılan durumlarda net iş günü bulmak istediğimizde TAMİŞGÜNÜ.ULUSL formülünü kullanmamız gerekir. İngilizce olarak NETWORKDAYS.INTL. Adından da anlayabileceğiniz gibi uluslar arası çalışma günü olarak çevirebileceğimiz bir anlamı var. Gelelim formülün yazılımına:
=TAMİŞGÜNÜ.ULUSL(başlangıç_tarihi; bitiş_tarihi; [hafta_sonu]; [tatiller])
TAMİŞGÜNÜ formülünden farklı olarak burada yine isteğe bağlı olarak hafta_sonu olarak bir değer girmeniz gerekecek. Bu değeri boş bırakabilirsiniz. Boş bırakır iseniz Cumartesi ve Pazar hafta sonu tatili olarak alınır. Aşağıdaki listede hafta sonu kısmına girebileceğiniz değerler ve anlamını gösterdim.
Varsayılan, 1 Cumartesi, Pazar
2 Pazar, Pazartesi
3 Pazartesi, Salı
4 Salı, Çarşamba
5 Çarşamba, Perşembe
6 Perşembe, Cuma
7 Cuma, Cumartesi
11 Sadece Pazar
12 Sadece Pazartesi
13 Sadece Salı
14 Sadece Çarşamba
15 Sadece Perşembe
16 Sadece Cuma
17 Sadece Cumartesi
Bu tablodaki değerler haricinde hafta sonu tatil günleri 1 ile ifade edilecek şekilde ve ilk karakter Pazartesi gününü göstermek üzere “1100000” şeklinde bir metin de girilebilir. Bu örnekte Pazartesi ve Salı tatil günü anlamına gelmektedir. Eğer buraya “0” yazılır ise hafta sonu tatili yok demektir.
Formülün geri kalan kısmı NETİŞGÜNÜ formülü ile aynı.
Aşağıdaki resimde formülün yazılımı, sonucu ve açıklamasını görebilirsiniz.
TAMİŞGÜNÜ.ULUSL formülü yazılımı ve sonucu
Bir tarihe belli bir iş günü eklendimizde veya çıkardığımızda hangi tarihe ulaşacağımızı bulmak için bu formülü kullanıyoruz. Örnek olarak imalat için belli bir süre alacak işlemlerin hangi iş gününde bitebileceğini hesaplamak için kullanılabiliriz. Hemen formülün yazımına bakalım:
=İŞGÜNÜ(başlangıç_tarihi; gün_sayısı; [tatiller])
Burada da TAMİŞGÜNÜ formülünde olduğu gibi bir başlangıç tarihi ve bu tarihten itibaren sayılacak gün adedi girilir. Ve tabii tatil günlerini de bir aralık olarak eklersek tatil günleri de dikkate alınarak girilen günler başlangıç tarihine eklenerek en yakın iş günü bulunur. Eğer gün olarak eksi bir rakam girersek geçmiş bir tarih değeri elde edilir.
Aşağıdaki resimde formülün nasıl yazıldığını ve sonuçlarını görebilirsiniz.
İşGünü Formülü Yazılımı ve Sonuçları
Yine TAMİŞGÜNÜ formülüne benzer bir şekilde varsayılan olarak Cumartesi ve Pazar günleri hafta sonu tatili olarak alınır. Eğer farklı hafta sonu tatil günleri uygulanıyorsa ya d a sadece Pazar günü hafta sonu tatili olarak alınacak ise bu durumda İŞGÜNÜ.ULUSL formülünü kullanmak gerekecektir.
Bu formül de İŞGÜNÜ formülünün hafta sonu tatilleri farklı olan veya sadece tek gün hafta sonu tatili olduğunda kullanılması gereken formüldür. Bu açıdan bakıldığında TAMİŞGÜNÜ.ULUSL formülü ile benzerlik gösterir. Yazılımı da yine bu formüle benzer olarak aşağıdaki gibidir.
=İŞGÜNÜ.ULUSL(başlangıç_tarihi; gün_sayısı; [hafta_sonu]; [tatiller])
Yazılımdan da anlaşıldığı üzere hafta sonu ve tatiller bilgileri isteğe göre girilebilir.
Bu formülde de hafta sonu tatilleri için aşağıdaki tabloda yer alan değişkenler kullanılabilir veya “1100000” gibi 7 karakterden oluşan, ilk karakter Pazartesiden başlayan ve 1 değeri hafta sonu tatilini gösterecek şekilde bir metin girilebilir.
Tatil günleri için de diğer formüllerde olduğu gibi tarih değeri, tarih içeren bir hücre veya aralık girebilirsiniz.
Aşağıdaki resimde formülün yazılımı, açıklamaları ve sonuçlarını görebilirsiniz.
ISGUNU.ULUSL Formülü Yazılımı, Sonucu ve Açıklaması
Excel’de tarih değerinin bir rakam olarak saklandığını söylemiştim. Bu nedenle farklı kaynaklardan gelen tarih verileri bazı durumlarda Excel tarafından tarih olarak algılanmayabiliyor. Örnek olarak bazen tarihler metin olarak gelebiliyor veya gün-ay-yıl ayrı sütunlarda veya satırlarda gelebiliyor. Bu gibi durumda gelen bu verinin tarih olarak dönüştürülmesi için kullanacağımız formüldür. Yazılışı:
=TARİH(yıl; ay; gün)
şeklindedir. Formülün İngilizcesi DATE olarak yazılır. Bu formülde rakam olarak yıl, ay ve gün değerlerini sırası ile girerek veya bu değerlerin yer aldığı hücre adresini yazarak ilgili değerlerin tarih olarak dönüştürülmesini sağlarız. Dikkat edilecek konu gün olarak sıfır yazılır ise bir önceki ayın son günü elde edilir.
Aşağıdaki resimde metin olarak gelmiş bir tarih bilgisinin önce bölünerek yıl-ay-gün haline döndürülmesi ve nihayetinde tarih formatına çevrilmesini görebilirsiniz.
Tarih formülü yazılımı ve sonucu
SAĞDAN İşlevi Excel’de metin işleme için kullanılan bir formüldür. İngilizce Excel’de RIGHT olarak kullanılır. Amacı bir metnin içinde en sağda yer alan ilk karakterden başlayarak girilen adet kadar karakteri okuyarak yeni bir metin oluşturmaktır. Yazılışı: =SAĞDAN(Metin, [sayı_karakterler]) şeklindedir. Metin yerine isterseniz çift tırnak içinde bir metin yazabilir veya metin içeren bir hücre seçebilirsiniz. Formülde dikkat […]
İngilizce karşılığı CHAR formülüdür. Girilen rakama göre ilgili ASCII karakteri geri döndürür. Örnek olarak DAMGA(65) formülünün sonucu A harfidir. ASCII tablosu hazırlamak için kullanabilirsiniz. Bilgisayarlarda tanımlı 255 tane ASCII karakteri olduğundan 255’ten büyük bir rakam girerseniz #DEĞER hatası alırsınız. Aşağıda DAMGA formülü ile ilgili bir kaç örnek gösterdim.