Formuła ogólna
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Podsumowanie
Jeśli chcesz przekonwertować datę na format daty juliańskiej w programie Excel, możesz to zrobić, tworząc formułę korzystającą z funkcji TEKST, ROK i DATA.
tło
„Format daty juliańskiej” odnosi się do formatu, w którym roczna wartość daty jest łączona z „porządkowym dniem dla tego roku” (tj. 14, 100 itd.) W celu utworzenia znacznika daty.
Istnieje kilka odmian. Data w tym formacie może zawierać 4-cyfrowy rok (rrrr) lub dwucyfrowy rok (rr), a numer dnia może, ale nie musi, być uzupełniony zerami, aby zawsze zawierał 3 cyfry. Na przykład dla daty 21 stycznia 2017 r. Możesz zobaczyć:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Rozwiązanie
W przypadku roku dwucyfrowego + numeru dnia bez dopełnienia:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Dla roku dwucyfrowego + numer dnia uzupełniony zerami w 3 miejscach:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Dla czterocyfrowego roku + numer dnia uzupełniony zerami w 3 miejscach:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Wyjaśnienie
Ta formuła tworzy wynik końcowy w 2 częściach, połączonych przez konkatenację z operatorem ampersand (&).
Po lewej stronie znaku ampersand generujemy wartość roku. Aby wyodrębnić 2-cyfrowy rok, możemy użyć funkcji TEKST, która może zastosować format liczbowy w formule:
TEXT(B5,"yy")
Aby wyodrębnić cały rok, użyj funkcji ROK:
YEAR(B5)
Po prawej stronie znaku ampersand musimy określić dzień roku. Robimy to, odejmując ostatni dzień poprzedniego roku od daty, z którą pracujemy. Ponieważ daty to tylko liczby seryjne, to da nam „n-ty” dzień roku.
Aby uzyskać ostatni dzień roku poprzedniego, używamy funkcji DATA. Jeśli podasz DATĘ wartość roku i miesiąca oraz zero dla dnia, otrzymasz ostatni dzień poprzedniego miesiąca. Więc:
B5-DATE(YEAR(B5),1,0)
daje nam ostatni dzień poprzedniego roku, czyli w tym przykładzie 31 grudnia 2015 roku.
Teraz musimy uzupełnić wartość dnia zerami. Ponownie możemy użyć funkcji TEKST:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Odwrócona data juliańska
Jeśli chcesz przekonwertować datę juliańską z powrotem na zwykłą datę, możesz użyć formuły, która analizuje datę juliańską i przeprowadza ją przez funkcję daty z miesiącem 1 i dniem równym „n-temu” dniu. Na przykład utworzyłoby to datę z rrrrdd daty juliańskiej, takiej jak 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Jeśli masz tylko numer dnia (np. 100, 153 itd.), Możesz zakodować rok na stałe i wstawić dzień w ten sposób:
=DATE(2016,1,A1)
Gdzie A1 zawiera numer dnia. Działa to, ponieważ funkcja DATE wie, jak dostosować wartości spoza zakresu.
