Γενική φόρμουλα
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Περίληψη
Εάν πρέπει να μετατρέψετε μια ημερομηνία σε μορφή ημερομηνίας Julian στο Excel, μπορείτε να το κάνετε δημιουργώντας έναν τύπο που χρησιμοποιεί τις συναρτήσεις TEXT, YEAR και DATE.
Ιστορικό
Η "μορφή ημερομηνίας Julian" αναφέρεται σε μια μορφή όπου η τιμή έτους μιας ημερομηνίας συνδυάζεται με την "κανονική ημέρα για το συγκεκριμένο έτος" (δηλαδή 14η ημέρα, 100η ημέρα κ.λπ.) για να σχηματίσει μια σφραγίδα ημερομηνίας.
Υπάρχουν πολλές παραλλαγές. Μια ημερομηνία σε αυτήν τη μορφή μπορεί να περιλαμβάνει ένα τετραψήφιο έτος (εεεε) ή ένα διψήφιο έτος (εε) και ο αριθμός της ημέρας μπορεί ή όχι να είναι γεμάτος με μηδενικά για πάντα χρήση 3 ψηφίων. Για παράδειγμα, για την ημερομηνία 21 Ιανουαρίου 2017, ενδέχεται να δείτε:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Λύση
Για διψήφιο αριθμό έτους + ημέρα χωρίς χρήση παραγεμίσματος:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Για διψήφιο έτος + αριθμό ημέρας με μηδενικά σε 3 θέσεις:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Για έναν τετραψήφιο αριθμό + έναν αριθμό ημέρας με μηδενικά σε 3 θέσεις:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Εξήγηση
Αυτός ο τύπος δημιουργεί το τελικό αποτέλεσμα σε 2 μέρη, ενώνονται με συνεννόηση με τον χειριστή ampersand (&).
Στα αριστερά του συμπλέγματος, δημιουργούμε την τιμή του έτους. Για να εξαγάγουμε ένα διψήφιο έτος, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση TEXT, η οποία μπορεί να εφαρμόσει μια μορφή αριθμού μέσα σε έναν τύπο:
TEXT(B5,"yy")
Για να εξαγάγετε ένα ολόκληρο έτος, χρησιμοποιήστε τη συνάρτηση YEAR:
YEAR(B5)
Στη δεξιά πλευρά του αμπέρ και πρέπει να καταλάβουμε την ημέρα του χρόνου. Αυτό το κάνουμε αφαιρώντας την τελευταία ημέρα του προηγούμενου έτους από την ημερομηνία με την οποία συνεργαζόμαστε. Επειδή οι ημερομηνίες είναι απλώς σειριακοί αριθμοί, αυτό θα μας δώσει την "η" ημέρα του χρόνου.
Για να λάβουμε την τελευταία ημέρα του προηγούμενου έτους, χρησιμοποιούμε τη συνάρτηση DATE. Όταν δίνετε στις DATE αξία έτους και μήνα και μηδέν για την ημέρα, λαμβάνετε την τελευταία ημέρα του προηγούμενου μήνα. Ετσι:
B5-DATE(YEAR(B5),1,0)
μας δίνει την τελευταία ημέρα του προηγούμενου έτους, που είναι το 31 Δεκεμβρίου 2015 στο παράδειγμα.
Τώρα πρέπει να συμπληρώσουμε την τιμή της ημέρας με μηδενικά. Και πάλι, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Αντίστροφη ημερομηνία Julian
Εάν πρέπει να μετατρέψετε μια ημερομηνία Julian σε κανονική ημερομηνία, μπορείτε να χρησιμοποιήσετε έναν τύπο που αναλύει την ημερομηνία Julian και την εκτελεί μέσω της συνάρτησης ημερομηνίας με ένα μήνα 1 και ημέρα ίση με την "nth" ημέρα. Για παράδειγμα, αυτό θα δημιουργούσε μια ημερομηνία από μια ημερομηνία Julian yyyyddd όπως το 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Εάν έχετε απλώς έναν αριθμό ημέρας (π.χ. 100, 153, κ.λπ.), μπορείτε να κωδικοποιήσετε τον χρόνο και να εισαγάγετε την ημέρα ως εξής:
=DATE(2016,1,A1)
Όπου το Α1 περιέχει τον αριθμό ημέρας. Αυτό λειτουργεί επειδή η συνάρτηση DATE ξέρει πώς να προσαρμόζεται για τιμές που είναι εκτός εμβέλειας.
