Công thức chung
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Tóm lược
Nếu bạn cần chuyển đổi ngày tháng sang định dạng ngày tháng Julian trong Excel, bạn có thể thực hiện việc này bằng cách xây dựng công thức sử dụng các hàm TEXT, YEAR và DATE.
Lý lịch
"Định dạng ngày Julian" đề cập đến một định dạng trong đó giá trị năm của ngày được kết hợp với "ngày thứ tự của năm đó" (tức là ngày 14, ngày thứ 100, v.v.) để tạo thành một dấu ngày.
Có một số biến thể. Ngày ở định dạng này có thể bao gồm năm 4 chữ số (yyyy) hoặc năm hai chữ số (yy) và số ngày có thể có hoặc không được đệm bằng số 0 để luôn sử dụng 3 chữ số. Ví dụ: đối với ngày 21 tháng 1 năm 2017, bạn có thể thấy:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Giải pháp
Đối với năm có hai chữ số + số ngày không sử dụng phần đệm:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Đối với năm có hai chữ số + số ngày được đệm bằng các số không đến 3 vị trí:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Đối với năm có bốn chữ số + số ngày được đệm bằng các số không đến 3 vị trí:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Giải trình
Công thức này xây dựng kết quả cuối cùng thành 2 phần, được nối bằng cách ghép với toán tử dấu và (&).
Ở bên trái của ký hiệu và, chúng tôi tạo ra giá trị năm. Để trích xuất năm có 2 chữ số, chúng ta có thể sử dụng hàm TEXT, hàm này có thể áp dụng định dạng số bên trong công thức:
TEXT(B5,"yy")
Để trích xuất một năm đầy đủ, hãy sử dụng hàm YEAR:
YEAR(B5)
Ở bên phải của dấu và chúng ta cần tìm ra ngày trong năm. Chúng tôi làm điều này bằng cách trừ ngày cuối cùng của năm trước cho ngày chúng tôi đang làm việc. Vì ngày tháng chỉ là số sê-ri, điều này sẽ cho chúng ta ngày "thứ n" trong năm.
Để lấy ngày cuối cùng của năm trước, chúng ta sử dụng hàm DATE. Khi bạn cung cấp DATE một giá trị năm và tháng và số 0 cho ngày, bạn sẽ nhận được ngày cuối cùng của tháng trước. Vì thế:
B5-DATE(YEAR(B5),1,0)
cho chúng ta ngày cuối cùng của năm trước, đó là ngày 31 tháng 12 năm 2015 trong ví dụ.
Bây giờ chúng ta cần chèn giá trị ngày bằng các số không. Một lần nữa, chúng ta có thể sử dụng hàm TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Đảo ngược ngày Julian
Nếu bạn cần chuyển đổi ngày Julian trở lại thành một ngày thông thường, bạn có thể sử dụng công thức phân tích cú pháp ngày Julian và chạy nó thông qua hàm ngày với tháng là 1 và ngày bằng ngày "thứ n". Ví dụ: điều này sẽ tạo một ngày từ một ngày Julian năm yyyyddd như 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Nếu bạn chỉ có một số ngày (ví dụ: 100, 153, v.v.), bạn có thể mã hóa năm và chèn ngày sau:
=DATE(2016,1,A1)
Trong đó A1 chứa số ngày. Điều này hoạt động vì hàm DATE biết cách điều chỉnh các giá trị nằm ngoài phạm vi.
