Excel'de Tarih ve Saat Hesaplamaları Nasıl Yapılır?

Excel tarihi ve saati nasıl saklar?

Tarih ve saat hesaplarının mantığı, Excel'in bunları nasıl sakladığını anlamakla başlar. Excel, tarihleri ardışık seri sayıları olarak tutar: her gün bir önceki günden bir fazla bir sayıdır. Saat ise günün kesri olarak saklanır; öğle vakti günün yarısına karşılık gelir. Hücrenin görünümü biçimlendirmeyle belirlenir, değeri ise her zaman bir sayıdır.

Bu yapının pratik sonuçları var. Tarihlerle toplama ve çıkarma doğrudan çalışır: bir tarihe sayı eklemek ileri bir tarih verir, tarihleri birbirinden çıkarmak aradaki gün sayısını verir. Tarih içeren bir hücre garip bir sayı gösteriyorsa sorun değerde değil biçimdedir; hücre biçimini tarihe çevirmek yeterlidir. Tersine, tarih gibi görünen ama sola hizalanan bir hücre çoğunlukla metindir ve hesaplamaya girmez; bu durumun çözümü metin tarihler bölümündedir.

Bugünün tarihi ve yıl, ay, gün parçaları

BUGÜN işlevi, her açılışta ve yeniden hesaplamada geçerli tarihi verir; ŞİMDİ işlevi buna saati de ekler. Rapor başlıklarında, yaş ya da kıdem hesabında ve vadesi yaklaşan kayıtların işaretlenmesinde sık kullanılır. Dikkat edilmesi gereken nokta, sonucun sabit olmamasıdır: dosyayı bir hafta sonra açtığınızda değer değişir. Bir işlemin tarihini sabit kaydetmek istiyorsanız tarihi hücreye değer olarak yazın.

Bir tarihin parçalarını YIL, AY ve GÜN işlevleri ayırır. Veriniz Excel tablosu olarak biçimlendirilmişse yapılandırılmış başvuruyla yazmak formülü okunur kılar: =YIL([@SatışTarihi]) satışın yılını, =AY([@SatışTarihi]) ayını verir. Bu iki yardımcı sütun pivot tabloda aylık toplam almayı, doğum ayına göre gruplamayı ya da faturaları dönemlere bölmeyi kolaylaştırır.

Tersi yön için TARİH işlevi vardır: =TARİH([@Yıl];[@Ay];[@Gün]) ayrı sütunlarda tutulan yıl, ay ve gün bilgisini tek bir gerçek tarihe çevirir. Vadesi yaklaşan kayıtları bulmak için =[@VadeTarihi]-BUGÜN() formülü kalan gün sayısını verir; bu sütuna koşullu biçimlendirme bağlamak, yaklaşan vadeleri renkle işaretlemenin en kısa yoludur.

Formüllerdeki bağımsız değişken ayırıcısı bölgesel ayara bağlıdır. Türkçe bölge ayarlı Excel'de ayırıcı genellikle noktalı virgüldür; İngilizce kaynaklardan kopyalanan virgüllü formüller bu yüzden hata verebilir.

Tarihler arasındaki farkı hesaplamak

En basit yöntem çıkarmadır: =[@Bitiş]-[@Başlangıç] aradaki gün sayısını verir. Sonucu sayı biçiminde göstermeyi unutmayın; Excel bazen sonucu tarih biçiminde gösterir ve anlamsız bir tarih çıkar.

Tam yıl, tam ay ya da kalan gün gerektiğinde ETARİHLİ işlevi kullanılır. İngilizce Excel'deki adı DATEDIF'tir; Türkçe Excel'de =TARİHFARKI(...) yazmak işlevi tanınmayan ada çevirir ve hata verir. Söz dizimi ETARİHLİ(başlangıç_tarihi; bitiş_tarihi; birim) biçimindedir. Birim değerleri tırnak içinde yazılır: • "Y": dönemdeki tam yıl sayısı. • "M": dönemdeki tam ay sayısı. • "D": dönemdeki gün sayısı. • "YM": yıllar yok sayılarak kalan ay farkı. • "YD": yıllar yok sayılarak gün farkı. • "MD": aylar ve yıllar yok sayılarak gün farkı. Microsoft bu birimin bilinen sınırlamaları olduğunu ve yanlış sonuç verebileceğini belirtip kullanılmamasını önerir.

Yaş için: =ETARİHLİ([@DoğumTarihi];BUGÜN();"Y"). Hizmet süresini yıl ve ay olarak yazmak için iki birimi birleştirin: =ETARİHLİ([@GirişTarihi];BUGÜN();"Y")&" yıl "&ETARİHLİ([@GirişTarihi];BUGÜN();"YM")&" ay". Başlangıç tarihi bitişten büyükse işlev hata değeri döndürür; tarihlerin sırasını denetleyin. Microsoft işlevi eski Lotus çalışma kitaplarıyla uyumluluk için sunduğunu belirtir; bu yüzden kritik hesaplarda sonucu birkaç örnek tarihle elle karşılaştırın.

Bu işlev özellikle kıdem tazminatı gibi hukuki sonuç doğuran hesaplarda yalnızca bir yardımcı olarak kullanılmalı, sonuç ilgili mevzuat ve uzman görüşüyle teyit edilmelidir.

İş günü hesapları: tatil ve hafta sonu hariç

Proje planlamada, teslim süresi hesabında ve vade takibinde takvim günü yerine iş günü sayılır. Bu iş için ayrı işlevler vardır: • TAMİŞGÜNÜ: İki tarih arasındaki tam çalışma günü sayısını verir. Örnek: =TAMİŞGÜNÜ([@Başlangıç];[@Bitiş];Tatiller). Hafta sonları ve Tatiller listesindeki günler sayılmaz. • İŞGÜNÜ: Bir başlangıç tarihinden belirli sayıda iş günü sonrasındaki tarihi verir. Örnek: =İŞGÜNÜ([@SiparişTarihi];[@TeslimSüresi];Tatiller) teslim tarihini hesaplar.

Tatiller burada ayrı bir sayfadaki tarih listesine verilmiş tanımlı addır. Listeyi Formüller sekmesindeki ad tanımlama aracıyla adlandırmak, her formülde aynı aralığı yeniden seçme zahmetini ve aralık kayması hatasını ortadan kaldırır. Liste her yıl güncellenmelidir; dini bayramların tarihi her yıl değiştiği için resmî takvimden kontrol ederek hazırlanmalıdır.

Hafta sonu Cumartesi ve Pazar olmayan işletmeler için TAMİŞGÜNÜ.ULUSL kullanılır. Hafta sonu bağımsız değişkenine, pazartesiden başlayarak her günü bir karakterle gösteren yedi karakterlik bir metin verilir: sıfır çalışma gününü, bir ise tatil gününü belirtir. Böylece örneğin yalnızca pazar ve pazartesi kapalı olan bir mağaza takvimini doğru yansıtır.

Saat ve süre hesapları

Saatler gün kesri olduğu için toplama ve çıkarma saat hesabında da çalışır: =[@Çıkış]-[@Giriş] çalışılan süreyi verir. Gece yarısını aşan vardiyada bitiş saati başlangıçtan küçük görünür ve sonuç eksiye düşer. Bunu düzeltmenin kısa yolu karşılaştırmayı formüle eklemektir: =[@Çıkış]-[@Giriş]+([@Çıkış]<[@Giriş]). Karşılaştırma doğruysa formül bir tam gün ekler, yanlışsa hiçbir şey eklemez.

Saatlik sürelerin toplamı bir günü aşarsa Excel varsayılan saat biçiminde günü atlayıp yalnız kalan saati gösterir. Toplam hücresine köşeli parantezli saat biçimini uygulayın; İngilizce arayüzde bu biçim [h]:mm olarak geçer, Türkçe arayüzde aynı biçim Hücreleri Biçimlendir penceresinin Özel bölümünde saat kodu köşeli parantez içine alınarak yazılır.

Süreyi maaş ve fazla mesai hesabı için ondalık saate çevirmek gerekiyorsa sonucu günün saat sayısıyla, yani yirmi dörtle çarpın ve hücreyi sayı biçimine alın; çarpmadan bırakılan değer günün kesri olarak kalır. Örnek bir puantaj tablosunda giriş ve çıkış saatlerinden günlük süre, haftalık toplam ve fazla mesai bu yöntemle hesaplanır; öğle arası gibi mola süreleri ayrı bir sütunda düşülür.

Metin olarak gelen tarihler ve sık hatalar

Başka sistemlerden aktarılan verilerde tarihler sıklıkla metin gelir. Belirtileri: hücre sola hizalıdır, tarih biçimi uygulanınca değişmez, hesaplamalar hata verir ya da sonuç beklenenden farklıdır. Çözüm yolları: TARİHSAYISI işleviyle metni tarihe çevirmek (=TARİHSAYISI([@MetinTarih])), metni sütunlara ayırma sihirbazında tarih biçimi seçmek, parçaları ayırıp TARİH işleviyle birleştirmek ya da Power Query ile veri tipini düzeltmek.

Bölgesel ayarlar da hataya yol açar. Gün-ay-yıl sırasıyla yazılmış bir tarih, gün ve ay sırası farklı bir bölgesel ayarla açılırsa ay ve gün yer değiştirmiş okunabilir; TARİHSAYISI da metni bilgisayarın bölge ayarına göre yorumlar. Veri paylaşırken tarihleri belirsizliğe yer bırakmayan bir biçimde saklamak iyi bir alışkanlıktır.

Sık hatalar: sabit kalması gereken tarihi BUGÜN işleviyle yazmak, tatilleri hesaba katmadan iş günü saymak, tarih olarak girilmiş metni hesaplamaya sokmak, saat toplamını köşeli parantezsiz biçimlemek, Türkçe Excel'de ETARİHLİ yerine çeviri bir ad yazmak ve ETARİHLİ işlevinde başlangıç ile bitişin yerini karıştırmak.

Mindset programlarında bu konuyu nerede çalışırsınız?

Bu sayfadaki tarih ve saat işlevleri Mindset kataloğunda iki farklı düzeyde çalışılır. Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı) programı, Veri Analizi ve İleri Formüller ile İleri Seviye İşlevler ve İleri Tablo Yönetimi modüllerinde formül ağırlıklı çalışır; yapılandırılmış başvurular ve tablo yönetimi bu sayfadaki örneklerin dayandığı yapıdır.

Excel Orta Düzey programı ise tarih işlevlerini ayrı bir başlık olarak vermez; veri temizleme ve düzenli veri yapısı, koşullu fonksiyonlar ve pivot tablo modülleri metin tarihleri temizlemek ve tarih sütunlarını raporlamada kullanmak için zemin hazırlar. Formülleri sıfırdan öğrenen biri için Orta Düzey, puantaj ve vade tablosu kuracak biri için İleri Excel daha uygun başlangıçtır.