Genel formül
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Özet
Excel'de bir tarihi Jülyen tarih biçimine dönüştürmeniz gerekiyorsa, bunu METİN, YIL ve TARİH işlevlerini kullanan bir formül oluşturarak yapabilirsiniz.
Arka fon
"Jülyen tarih biçimi", bir tarihin yıl değerinin bir tarih damgası oluşturmak için "o yıl için sıra günü" (yani 14. gün, 100. gün vb.) İle birleştirildiği bir biçimi ifade eder.
Birkaç varyasyon var. Bu biçimdeki bir tarih 4 basamaklı bir yıl (yyyy) veya iki basamaklı bir yıl (yy) içerebilir ve gün numarası her zaman 3 basamak kullanmak için sıfırlarla doldurulabilir veya verilmeyebilir. Örneğin, 21 Ocak 2017 tarihi için şunu görebilirsiniz:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Çözüm
İki basamaklı bir yıl + dolgu içermeyen bir gün numarası için:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
İki basamaklı bir yıl + 3 basamaklı sıfırlarla doldurulmuş bir gün numarası için:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Dört basamaklı bir yıl + 3 haneye kadar sıfırlarla doldurulmuş bir gün numarası için:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Açıklama
Bu formül, nihai sonucu "ve" (&) operatörüyle birleştirilerek 2 parça halinde oluşturur.
Ve işaretinin solunda, yıl değerini oluşturuyoruz. 2 basamaklı bir yılı çıkarmak için, formülün içine bir sayı biçimi uygulayabilen TEXT işlevini kullanabiliriz:
TEXT(B5,"yy")
Tam bir yılı çıkarmak için YEAR işlevini kullanın:
YEAR(B5)
Ve işaretinin sağ tarafında yılın gününü bulmamız gerekiyor. Bunu çalıştığımız tarihten bir önceki yılın son gününü çıkararak yapıyoruz. Tarihler sadece seri numaraları olduğundan, bu bize yılın "n." Gününü verecektir.
Önceki yılın son gününü elde etmek için TARİH işlevini kullanıyoruz. TARİH bir yıl ve ay değeri ve gün için sıfır verdiğinizde, önceki ayın son gününü alırsınız. Yani:
B5-DATE(YEAR(B5),1,0)
bize bir önceki yılın son günü olan örnekte 31 Aralık 2015'i verir.
Şimdi gün değerini sıfırlarla doldurmamız gerekiyor. Yine, METİN işlevini kullanabiliriz:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Jülyen tarihini ters çevir
Jülyen tarihini normal bir tarihe geri dönüştürmeniz gerekiyorsa, Jülyen tarihini ayrıştıran ve tarih işlevinde 1 ay ve gün "n." Güne eşit olan bir formül kullanabilirsiniz. Örneğin, bu 1999143 gibi bir yyyyddd Jülyen tarihinden bir tarih oluşturur.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Yalnızca bir gün numaranız varsa (ör. 100, 153, vb.), Yılı sabit kodlayabilir ve bunu günü ekleyebilirsiniz:
=DATE(2016,1,A1)
A1, gün numarasını içerir. Bu işe yarar çünkü TARİH işlevi aralık dışındaki değerler için nasıl ayarlanacağını bilir.
