Elimizde belirli başlangıç ve bitiş tarihleri olduğunu varsayalım. Biz bu başlangıç bitiş tarihi arasında kalan aylarda toplam kaç gün olduğunu bulmak isteyelim. Örnek olarak bir projede çalışan kişilerin, projeye dahil olma ve projeden çıkma tarihlerine göre hangi ayda toplam kaç gün çalıştığını bulmak isteyelim. Aşağıda basit bir tabloda gösteriyorum:
1. Uygulama
Uygulamaya başlamadan önce sütun başlarında yazan ay adlarını metin olarak girmeyin. Ben burada 1/1/2020 gibi tarih girdim ve sonra hücre rakam biçimlendirmesini "Özel" olarak seçtikten sonra tür kutusuna "aaaa" yazdım (tırnaklar olmadan). Bu Excel'e girilen tarihin sadece ayını, kısaltma yapmadan metin olarak göstermesini söyler.
Neden böyle yaptığımı sorarsanız, tarih kıyaslamaları için bu hücredeki değeri kullanacağım. Eğer metin olarak girersem uzun bir formül ile bunu tarihe dönüştürmem gerek. Bu da işlemleri daha karmaşık hale getirir.
Formüldeki $ simgesinin tam olarak nerelerde olduğuna dikkat edin. Eğer karıştırırsanız formülleri yana doğru çoğalttığımızda başvurulan hücre referansları farklı hücrelere kayacağından çoğalttığınız formüller doğru değerleri vermez. Eğer $ işareti ile ilgili daha fazla bilgi almak istiyorsanız buradaki yazıyı okumanızı tavsiye ederim.
Yukarıdaki tabloyu B2 hücresinden başlayarak oluşturdum. Yazacağım formüllerde kullanacağım hücre adresleri de buna göre olacak. Eğer siz tabloyu farklı bir yerde oluşturur veya kendi tablonuzdaki değerler farklı hücrelerde ise lütfen formüllerdeki hücre başvuru adreslerini buna göre güncelleyin.
a. Başlangıç Tarihini Bulma
İlk yapmamız gereken işlem, başlangıç tarihi ile ayın ilk gününü kıyaslamak. Eğer başlangıç tarihi o ay içinde ise, ayın başladığı ilk günün tarihinden büyük olacaktır. Örnek olarak ayın başlangıç tarihi 1/1/2020 ve kişinin başlangıç tarihi 15/1/2020 ise bu formülle 15/1/2020 değerini geri döndürmem gerek.
Buradaki sihirli kelime büyük, büyük tarihi bulmak için kullanacağımız işlev de MAK işlevi:
=MAK(E$2;$C3)
Bu formül ile en büyük tarihi elde ediyorum, örnek olarak E3 hücresine yazarsanız 15/2/2020 tarihini elde edersiniz. Burada olduğu gibi kişinin başladığı tarih o ay içinde değil. Ancak şimdilik bunu dikkate almayacağım, ileride bunu çözecek bir formül daha yazacağız.
b. Bitiş Tarihini Bulma
İkinci aşamada kişinin projeden çıkış tarihi ile ayın bitiş tarihini kıyaslamam gerek. Eğer kişinin projeden çıkış tarihi o ay içinde ise, çıkış tarihi ayın son gününden küçük olacaktır. Burada da sihirli kelime küçük. Ama tabii bunu yapmadan önce ayın son gününün tarihini bulmam gerek.
Ayın son gününü bulmak için, ayın başlangıç tarihine 1 ay ekleyip 1 gün çıkarabilirim. Kullanacağım işlev SERİTARİH işlevi, bu işlev girilen bir başlangıç tarihine istenildiği kadar ay eklemek için kullanılıyor:
=SERİTARİH(E$2;1)-1
Bu formülü yazdığınızda 31/01/2020 tarihini elde edeceksiniz. Şimdi bunu projeden çıkış tarihi ile kıyaslayalım ve hangisi daha küçük ise onu alalım:
=MİN(SERİTARİH(E$2;1)-1;$D2)
Bu işlev ile eğer proje bitiş tarihi o ayın bitiş tarihinden önce ise proje bitiş tarihini, eğer büyük ise ayın son gününü elde ederiz. Örnek olarak formülü E3 hücresine yazarsak 31/1/2020 tarihini elde ederiz.
c. Başlangıç Bitiş Tarihleri Arasındaki Günü Bulma
Başlangıç, bitiş tarihlerine göre çalışılan toplam günü bulmak için yapmamız gereken şey basit, son tarihten ilk tarihi çıkarmak ve 1 eklemek.
=MİN(SERİTARİH(E$2;1)-1;$D2)-MAK(E$2;$C3)+1
Bu formülü E3 hücresine yazarak L7 aralığına çoğaltırsanız aşağıdaki gibi bir görüntü elde edersiniz:
Gördüğünüz gibi tabloda hem pozitif hem negatif değerler var, negatif değerler ilk maddede bahsettiğim sebep yüzünden. Negatif rakamlar aslında o ay bir çalışma olmadığı anlamına geliyor. Yani negatif rakamların yerine 0 değerini almam lazım, bunu da elde ettiğim değeri 0 ile karşılaştırıp büyük olanı alarak yapabilirim:
=MAK(0;MİN(SERİTARİH(E$2;1)-1;$D2)-MAK(E$2;$C3)+1)
Formülü E3 hücresine yazıp L7 aralığına kadar çoğaltırsanız istediğimiz gibi kişilerin hangi aylarda o proje için kaç gün çalıştığını bulmuş oluruz.
d. Net Çalışma Gününü Bulma
Buraya kadar bulduğumuz aylara göre toplam gün sayıları idi. Günlük olarak maliyet çıkarmak isterseniz işinizi çözer. Ancak sadece hafta içi çalışan, tatil günleri çalışmayan, insan gibi, tatil günleri hariç net çalışma gününü nasıl bulabiliriz derseniz formülü biraz değiştirmemiz gerekiyor.
Başlangıç tarihini ve bitiş tarihini önceki adımlarda bulmuştuk. Bu tarihleri TAMİŞGÜNÜ veya eğer sadece Pazar günü tatil ise TAMİŞGÜNÜ.ULUSL işlevi ile kullanmamız gerekiyor:
=TAMİŞGÜNÜ(MAK(E$2;$C3);MİN(SERİTARİH(E$2;1)-1;$D2);)
=TAMİŞGÜNÜ.ULSL(MAK(E$2;$C3);MİN(SERİTARİH(E$2;1)-1;$D2);11;)
Bu formüller ile bir önceki adımda olduğu gibi negatif rakamlar elde ediliyor. Bu nedenle bir yine aynı şekilde MAK işlevi ile beraber kullanacağım:
=MAX(0;TAMİŞGÜNÜ(MAK(E$2;$C3);MİN(SERİTARİH(E$2;1)-1;$D2);))
=MAK(0;TAMİŞGÜNÜ.ULSL(MAK(E$2;$C3);MİN(SERİTARİH(E$2;1)-1;$D2);11;))
Artık sadece aylık toplam çalışma gününü elde etmiş oldum.
Not: Eğer her iki tarih arasında resmi tatiller var ise bunları gün gün (başlangıç bitiş tarihi olarak değil her tatil günü için tarih girerek) bir aralığa yazarsanız TAMİŞGÜNÜ(baş_tarihi;bitiş_tarihi;[TATİL ARALIĞI]) veya TAMİŞGÜNÜ.ULUSL(baş_tarihi;bitiş_tarihi;[hafta_sonu];[TATİL ARALIĞI]) şeklinde yazabilirsiniz. Bu şekilde resmi tatiller hariç çalışılan günleri bulursunuz. Formüllerdeki Tarih Aralığı kısmına hazırlamış olduğunuz aralığın başvurusunu yazmanız gerekmekte.