Formula generik
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Ringkasan
Sekiranya anda perlu menukar tarikh menjadi format tarikh Julian di Excel, anda boleh melakukannya dengan membina formula yang menggunakan fungsi TEKS, TAHUN, dan TARIKH.
Latar belakang
"Format tarikh Julian" merujuk kepada format di mana nilai tahun tarikh digabungkan dengan "hari ordinal untuk tahun itu" (iaitu hari ke-14, hari ke-100, dll.) Untuk membentuk cap tarikh.
Terdapat beberapa variasi. Tarikh dalam format ini mungkin merangkumi tahun 4 digit (yyyy) atau tahun dua digit (yy) dan nombor hari mungkin atau mungkin tidak dilengkapi sifar untuk selalu menggunakan 3 digit. Contohnya, untuk tarikh 21 Januari 2017, anda mungkin melihat:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Penyelesaian
Untuk nombor dua digit tahun + sehari tanpa penggunaan padding:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Untuk angka dua digit + nombor sehari yang ditambah dengan sifar ke 3 tempat:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Untuk tahun empat digit + nombor sehari yang ditambah dengan sifar ke 3 tempat:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Penjelasan
Formula ini membina hasil akhir dalam 2 bahagian, bergabung dengan gabungan dengan operator ampersand (&).
Di sebelah kiri ampersand, kita menjana nilai tahun. Untuk mengekstrak tahun 2 digit, kita dapat menggunakan fungsi TEXT, yang dapat menerapkan format angka dalam formula:
TEXT(B5,"yy")
Untuk mengekstrak setahun penuh, gunakan fungsi TAHUN:
YEAR(B5)
Di sebelah kanan ampers dan kita perlu memikirkan hari sepanjang tahun. Kami melakukan ini dengan mengurangkan hari terakhir tahun sebelumnya dari tarikh kami bekerja. Oleh kerana tarikh hanya nombor siri, ini akan memberi kita hari "nth" dalam setahun.
Untuk mendapatkan hari terakhir tahun sebelumnya, kami menggunakan fungsi DATE. Apabila anda memberikan DATE nilai tahun dan bulan, dan sifar untuk hari, anda mendapat hari terakhir bulan sebelumnya. Jadi:
B5-DATE(YEAR(B5),1,0)
memberi kita hari terakhir tahun sebelumnya, iaitu 31 Disember 2015 dalam contoh.
Sekarang kita perlu menambah nilai hari dengan sifar. Sekali lagi, kita boleh menggunakan fungsi TEKS:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Balik tarikh Julian
Sekiranya anda perlu menukar tarikh Julian menjadi tarikh biasa, anda boleh menggunakan formula yang menguraikan tarikh Julian dan menjalankannya melalui fungsi tarikh dengan bulan 1 dan hari sama dengan hari "nth". Sebagai contoh, ini akan membuat tarikh dari tarikh Julian yyyyddd seperti 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Sekiranya anda hanya mempunyai nombor hari (mis. 100, 153, dsb.), Anda boleh membuat kod tahun dengan keras dan memasukkan hari ini:
=DATE(2016,1,A1)
Di mana A1 mengandungi nombor hari. Ini berfungsi kerana fungsi DATE tahu bagaimana menyesuaikan untuk nilai yang berada di luar jangkauan.
