Formula generica
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Sommario
Se è necessario convertire una data in un formato di data giuliano in Excel, è possibile farlo creando una formula che utilizzi le funzioni TESTO, ANNO e DATA.
sfondo
"Formato data giuliano" si riferisce a un formato in cui il valore dell'anno di una data è combinato con il "giorno ordinale per quell'anno" (ad esempio 14 ° giorno, 100 ° giorno, ecc.) Per formare un timbro data.
Esistono diverse varianti. Una data in questo formato può includere un anno a 4 cifre (aaaa) o un anno a due cifre (aa) e il numero del giorno può o non può essere riempito con zeri per utilizzare sempre 3 cifre. Ad esempio, per la data 21 gennaio 2017, potresti vedere:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Soluzione
Per un anno a due cifre + un numero di giorno senza riempimento, utilizzare:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Per un anno a due cifre + un numero di giorno riempito con zeri fino a 3 posizioni:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Per un anno a quattro cifre + un numero di giorno riempito con zeri fino a 3 posizioni:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Spiegazione
Questa formula costruisce il risultato finale in 2 parti, unite per concatenazione con l'operatore e commerciale (&).
A sinistra della e commerciale, generiamo il valore dell'anno. Per estrarre un anno a 2 cifre, possiamo utilizzare la funzione TESTO, che può applicare un formato numerico all'interno di una formula:
TEXT(B5,"yy")
Per estrarre un anno intero, utilizzare la funzione ANNO:
YEAR(B5)
Sul lato destro della e commerciale abbiamo bisogno di capire il giorno dell'anno. Lo facciamo sottraendo l'ultimo giorno dell'anno precedente dalla data con cui stiamo lavorando. Poiché le date sono solo numeri di serie, questo ci darà l '"ennesimo" giorno dell'anno.
Per ottenere l'ultimo giorno dell'anno dell'anno precedente, utilizziamo la funzione DATA. Quando dai a DATE un valore di anno e mese e uno zero per giorno, ottieni l'ultimo giorno del mese precedente. Così:
B5-DATE(YEAR(B5),1,0)
ci fornisce l'ultimo giorno dell'anno precedente, che è il 31 dicembre 2015 nell'esempio.
Ora dobbiamo riempire il valore del giorno con zeri. Di nuovo, possiamo usare la funzione TESTO:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Data giuliana inversa
Se è necessario riconvertire una data giuliana in una data normale, è possibile utilizzare una formula che analizza la data giuliana e la esegue tramite la funzione data con un mese di 1 e un giorno uguale all '"ennesimo" giorno. Ad esempio, questo creerebbe una data da una data giuliana aaaidgg come 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Se hai solo un numero di giorno (ad esempio 100, 153, ecc.), Puoi codificare l'anno e inserire il giorno questo:
=DATE(2016,1,A1)
Dove A1 contiene il numero del giorno. Questo funziona perché la funzione DATE sa come regolare i valori che sono fuori intervallo.
