Formula umum
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Ringkasan
Jika Anda perlu mengonversi tanggal ke format tanggal Julian di Excel, Anda bisa melakukannya dengan membuat rumus yang menggunakan fungsi TEXT, YEAR, dan DATE.
Latar Belakang
"Format tanggal Julian" mengacu pada format di mana nilai tahun dari sebuah tanggal digabungkan dengan "hari ordinal untuk tahun itu" (yaitu hari ke-14, hari ke-100, dll.) Untuk membentuk cap tanggal.
Ada beberapa variasi. Tanggal dalam format ini dapat menyertakan tahun 4-digit (yyyy) atau tahun dua digit (yy) dan nomor hari mungkin atau tidak boleh diisi dengan nol untuk selalu menggunakan 3 digit. Misalnya, untuk tanggal 21 Januari 2017, Anda mungkin melihat:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Larutan
Untuk tahun dua digit + nomor hari tanpa penggunaan padding:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Untuk tahun dua digit + nomor hari yang diisi dengan angka nol ke 3 tempat:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Untuk tahun empat digit + nomor hari yang diisi dengan nol ke 3 tempat:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Penjelasan
Rumus ini membangun hasil akhir dalam 2 bagian, digabungkan dengan penggabungan dengan operator ampersand (&).
Di sebelah kiri ampersand, kami menghasilkan nilai tahun. Untuk mengekstrak tahun 2 digit, kita dapat menggunakan fungsi TEXT, yang dapat menerapkan format angka di dalam rumus:
TEXT(B5,"yy")
Untuk mengekstrak satu tahun penuh, gunakan fungsi YEAR:
YEAR(B5)
Di sisi kanan ampersdan kita perlu mencari tahu hari dalam setahun. Kami melakukan ini dengan mengurangi hari terakhir tahun sebelumnya dari tanggal kami bekerja. Karena tanggal hanyalah nomor seri, ini akan memberi kita hari "n" dalam setahun.
Untuk mendapatkan hari terakhir tahun sebelumnya, kami menggunakan fungsi DATE. Jika Anda memberi DATE nilai satu tahun dan bulan, dan nol untuk hari, Anda mendapatkan hari terakhir dari bulan sebelumnya. Begitu:
B5-DATE(YEAR(B5),1,0)
memberi kita hari terakhir tahun sebelumnya, yaitu 31 Desember 2015 pada contoh.
Sekarang kita perlu mengisi nilai hari dengan nol. Sekali lagi, kita dapat menggunakan fungsi TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Tanggal terbalik Julian
Jika Anda perlu mengubah tanggal Julian kembali ke tanggal biasa, Anda dapat menggunakan rumus yang mengurai tanggal Julian dan menjalankannya melalui fungsi tanggal dengan bulan 1 dan hari sama dengan hari "n". Misalnya, ini akan membuat tanggal dari tanggal Julian yyyyddd seperti 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Jika Anda hanya memiliki nomor hari (misalnya 100, 153, dll.), Anda dapat membuat kode keras tahun dan memasukkan hari ini:
=DATE(2016,1,A1)
Dimana A1 berisi nomor hari. Ini berfungsi karena fungsi DATE tahu cara menyesuaikan nilai yang berada di luar jangkauan.
