Excel'de yaş ve kıdem süresi formülleri

Excel yaşı nasıl hesaplar?

Excel tarihleri arka planda sıra numarası olarak saklar; tarihler arasındaki fark gün sayısı verir. Bu yüzden bugünün tarihinden doğum tarihini çıkarıp yıla bölmek “yaklaşık” sonuç verir ama tam yaşı vermez. Yaşı doğru bulmak için doğum gününün bu yıl geçip geçmediğini de hesaba katmak gerekir.

İş yaşamında bu hesap personel listelerinde, yaş grubu raporlarında, emeklilik veya hak edişe kalan süre kontrollerinde ve kıdem tablolarında karşınıza çıkar. Yöntemler gruplara ayrılır: DATEDIF işlevi, YILORAN ile yıl kesri almak ve YIL, AY, GÜN bileşenleriyle kendi formülünü kurmak. Her birinin güçlü ve zayıf yanı vardır; hangisini seçeceğiniz sonucun nerede kullanılacağına bağlıdır.

DATEDIF ile tam yaş ve kıdem

En kısa yol DATEDIF işlevidir. Başlangıç tarihi, bitiş tarihi ve birim harfi alır. Birim olarak Y tam yılı, M tam ayı, D günü, YM yıl çıkarıldıktan sonra kalan ayı, MD ay çıkarıldıktan sonra kalan günü verir. Doğum tarihi Doğum sütununda olan bir tabloda tam yaş:

=DATEDIF([@Doğum];BUGÜN();"Y")

İşe giriş tarihinden “yıl, ay, gün” biçiminde kıdem metni için birimleri birleştirirsiniz:

=DATEDIF([@Giriş];BUGÜN();"Y")&" yıl "&DATEDIF([@Giriş];BUGÜN();"YM")&" ay "&DATEDIF([@Giriş];BUGÜN();"MD")&" gün"

DATEDIF eski bir uyumluluk işlevidir; formül önerisi listesinde çıkmayabilir ve Excel'in işlev ekleme penceresinde bulunmayabilir. Türkçe arayüzde kullandığınız sürümde işlevin adını bir hücrede deneyerek doğrulayın. Ayrıca MD birimi bazı tarih kombinasyonlarında, özellikle ay sonlarında hatalı sonuç verebilir; kıdemi gün hassasiyetinde raporlayacaksanız sonucu birkaç örnek tarihle elle kontrol edin.

DATEDIF kullanmadan tam yaş formülü

Formülün her sürümde ve her dilde tutarlı çalışmasını istiyorsanız bileşenlerle kurun. Mantık şudur: bugünün yılından doğum yılını çıkar, doğum günü bu yıl henüz gelmediyse bir eksilt.

=YIL(BUGÜN())-YIL([@Doğum])-(TARİH(YIL(BUGÜN());AY([@Doğum]);GÜN([@Doğum]))>BUGÜN())

Formülün son kısmı bu yılın doğum gününü TARİH ile kurar ve bugünle karşılaştırır. Karşılaştırma doğru ise Excel bunu bir sayı olarak çıkarır ve yaş bir eksilir. Bu sürüm uzun görünür ama işlevin sürümüne bağımlılığı yoktur ve formülü okuyan herkes mantığı adım adım takip edebilir.

Yıl kesri isterseniz YILORAN uygundur: =YILORAN([@Doğum];BUGÜN()). Sonuç ondalıklı yıl verir; tam yaş için sonucu TAMSAYI ile kırpın. YILORAN'ın varsayılan hesap tabanı, doğum günü civarında beklediğiniz tam sayıdan farklı sonuç üretebilir; tam yaşa ihtiyaç duyan yerlerde yukarıdaki bileşenli formülü ya da DATEDIF'i tercih edin.

Belirli bir tarihteki yaşı ve çıkış yapmış personelin kıdemini hesaplamak

BUGÜN() her açılışta değiştiği için geçmişe dönük raporlarda tehlikelidir: bir yıl önce hazırladığınız rapor bugünün tarihiyle yeniden hesaplanır. Raporun “bakış tarihi”ni ayrı bir hücreye yazıp adlandırın (örneğin BakışTarihi) ve formüllerde BUGÜN() yerine bu adı kullanın. Güncel rapor için hücreye =BUGÜN() yazarsınız, dondurulmuş rapor için sabit bir tarih girersiniz.

Çıkış yapmış çalışanlar için bitiş tarihi bugün değil, çıkış tarihidir. Tabloda Çıkış sütunu boş olabileceğinden bitişi koşullu seçin:

=DATEDIF([@Giriş];EĞER([@Çıkış]="";BakışTarihi;[@Çıkış]);"Y")

Aktif çalışanlarda boş hücre bakış tarihine döner, ayrılanlarda gerçek çıkış tarihi kullanılır. Böylece tek formül tüm sütun için çalışır ve ayrılmış personelin kıdemi her gün büyümez.

Yaş gruplarına ayırma ve özet rapor

Yaşı tek başına raporlamak nadirdir; çoğu zaman yaş aralığına göre dağılım istenir. Yaş sütununun yanına bir grup sütunu ekleyin. Bunun için küçük bir arama tablosu kurun: bir sütunda her grubun alt sınırı, yanında grup adı. Arama tablosunun alt sınırları küçükten büyüğe sıralı olmalıdır. Sonra yaş sütununa ARA işleviyle grup bulursunuz:

=ARA([@Yaş];GrupAltSınır;GrupAdı)

Yaklaşık eşleşme çalıştığı için yaş, hangi alt sınırdan büyük ya da eşitse o gruba düşer. Bu yapıyı yaş aralığı değiştiğinde yalnızca arama tablosunu güncelleyerek yönetirsiniz; formüle sabit sayı gömmezsiniz.

Ardından grup sütunuyla pivot tablo oluşturup gruba göre kişi sayısını ve departman kırılımını görebilirsiniz. Kıdem için de aynı yöntem geçerli: yıl bazlı kıdem sütununu gruplayıp “yeni başlayan, orta kıdem, deneyimli” benzeri bantlar tanımlayın. Bantların sınırlarını şirket politikası belirler; Excel yalnızca uygular.

Sık yapılan hatalar

Tarih hesaplarında hataların çoğu formülden değil, veriden gelir: • Metin olarak saklanan tarihler: Hücre sola yaslıysa ve tarih gibi davranmıyorsa metindir. Veri sekmesindeki Metni Sütunlara Dönüştür ile tarihe çevirin; aksi halde formül hata verir veya sıfırlı sonuç üretir. • Gün-ay sırası karışıklığı: Farklı kaynaklardan gelen dosyalarda ay-gün sırası değişebilir. Tüm tarihleri aynı biçimde görüp kontrol edin. • BUGÜN() ile geçmiş rapor: Yukarıda anlatıldığı gibi rapor her açıldığında kayar. • Doğum tarihi bitiş tarihinden sonra: DATEDIF başlangıç tarihi bitişten büyükse hata verir; veri girişinde veri doğrulama ile tarih sırasını denetleyin. • Yıl hesabını gün sayısına bölerek yapmak: Artık yıllar nedeniyle doğum günlerinde bir günlük sapma olur; tam yaş için yukarıdaki yöntemlerden birini kullanın. • Kıdem tazminatı gibi hukuki hesapları tek formüle bırakmak: Tazminat kıdemi iş mevzuatına göre belirlenir; Excel yalnızca takvim farkını verir.

Mindset programlarında bu beceri nerede çalışılır?

Yaş ve kıdem hesabı, Excel'de tarih işlevleri, koşullu mantık ve pivot analizinin İK verisine uygulandığı tipik bir iştir. Mindset'in Excel Orta Düzey eğitimi Koşullu Fonksiyonlar ve Çok Katmanlı Hesaplamalar ile Pivot Tablo Ustalığı modüllerini içerir; bu başlıklar yukarıdaki formül ve grup raporu mantığının temelidir.

Excel ile İK Analitiği eğitimi ise İK Analitiğine Giriş ve Veri Haritası ile Devir Hızı ve Elde Tutma Analizi gibi modüllerle, yaş ve kıdem dağılımını işe alım ve elde tutma kararlarına bağlamak isteyen İK ekipleri için uygundur. Kurumsal ekiplerde personel sicil verisini ortak standartla hazırlamak bu eğitimlerin doğal kullanım alanıdır.