Ένας χρόνος, ένας μήνας, μια ημέρα…. Η συνάρτηση DATEDIF

DimitrisHorn2

‘ένα εκατομμύριο, εκατόν μία χιλιάδες, εκατόν μία και δέκα’. Ο αξέχαστος Δημήτρης Χορν, στο ρόλο του ταμία, ψάχνει για το χαμένο εκατομμύριο της Τράπεζας. Εδώ ψάχνουμε για χαμένες ημέρες στο excel…

Στο κελί Α1 ενός φύλλου γράψτε μια οποιαδήποτε ημερομηνία και στο κελί Β1 μια άλλη μεταγενέστερη. Σε ένα τρίτο κελί γράψτε τη συνάρτηση:

=DATEDIF(A1;B1;"m")

Θα σας επιστραφεί ένας αριθμός που θα δίνει τη συνολική διαφορά Β1-Α1 σε ακέραιους μήνες. Η συνάρτηση DATEDIF αν και δεν εμφανίζεται στον κατάλογο των συναρτήσεων, υπάρχει σε όλες τις εκδόσεις του excel. Η επίσημη βοήθεια για τη συνάρτηση (εδώ) λέει ότι παρέχετε για λόγους συμβατότητας με άλλα προγράμματα.
Η πλήρης σύνταξή της είναι:
=DATEDIF(αρχική ημερομηνία ; τελική ημερομηνία ; μονάδα)
Η συνάρτηση υπολογίζει τη διαφορά των δύο ημερομηνιών και την αποδίδει στη μορφή που καθορίζει το όρισμα ‘μονάδα’, το οποίο μπορεί να πάρει μία από τις εξής τιμές:

"y", "ym", "md", "m", "d", "yd"

Οι τρεις πρώτες αν συνδυαστούν δίνουν τη διαφορά των ημερομηνιών στη μορφή: έτη-μήνες-ημέρες. Θα δούμε όλες τις περιπτώσεις παρακάτω αναλυτικά και για κάθε περίπτωση θα δούμε και εναλλακτικούς τύπους φύλλου.
Σημειώστε ότι:
• Σε όλους τους παρακάτω τύπους, Α1 είναι το κελί που περιέχει την αρχική ημερομηνία, και Β1 το κελί που περιέχει την τελική. (I)
• Στη διαφορά μεταξύ τελικής και αρχικής ημερομηνίας, η αρχική προσμετρείται στη διαφορά ενώ η τελική όχι. Αν θέλετε να προσμετρηθεί και η τελική ημέρα, σε όλους τους τύπους που ακολουθούν αντικαταστήστε το όρισμα B1 με B1+1
• Μερικές από τις συναρτήσεις, μορφοποιούν αυτόματα το κελί που θα εισαχθούν, σε μορφή ημερομηνίας. Στην περίπτωση αυτή, μετά την εισαγωγή της συνάρτησης, αλλάξτε τη μορφή του κελιού σε μια μορφή αριθμού ή σε «Γενική»

(1) Το όρισμα ‘μονάδα’ είναι y
Η συνάρτηση:

=DATEDIF(A1;B1;"y")

επιστρέφει τη διαφορά των ημερομηνιών σε ακέραια έτη. Το τυχόν υπόλοιπο σε μήνες και ημέρες αγνοείται. Παραδείγματα:

αρχική           τελική           έτη
1/5/2009         15/11/2011       2
1/5/2009         1/5/2011         2
1/5/2009         30/4/2011        1
1/5/2009         15/3/2011        1

Απολύτως ισοδύναμοι είναι οι παρακάτω τύποι:


=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));YEAR(B1)-YEAR(A1)-1;YEAR(B1)-YEAR(A1))

=YEAR(B1)-YEAR(A1)-(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)))

Παρατηρήστε ότι αν η ημερομηνία (μήνας και ημέρα μόνο, χωρίς το έτος) στην τελική ημερομηνία είναι μεγαλύτερη ή ίση από την ημερομηνία στην αρχική τότε τα έτη είναι απλά η διαφορά των ετών. Αν μήνας και ημέρα της τελικής είναι μικρότερη από μήνα και ημέρα αρχικής, τότε η διαφορά των ετών πρέπει να ελαττωθεί κατά 1. Έτσι, στους δύο εναλλακτικούς τύπους, τοποθετώ μήνα και ημέρα σε ένα τυχαίο έτος (κατά προτίμηση δίσεκτο για να καλύψω και την περίπτωση 29-Φεβ),για να συγκρίνω μήνα και ημέρα τελικής και αρχικής, και ανάλογα η συνάρτηση επιστρέφει τη απλή διαφορά των ετών ή τη διαφορά ελαττωμένη κατά 1.

(2) Το όρισμα ‘μονάδα’ είναι ym
Η συνάρτηση:

=DATEDIF(A1;B1;"ym")

επιστρέφει τους υπόλοιπους ακέραιους μήνες (της διαφοράς Β1-Α1) που απομένουν, αφού αφαιρέσει τους μήνες που αντιστοιχούν σε ολόκληρα έτη. Το τυχόν υπόλοιπο σε ημέρες αγνοείται. Παραδείγματα:

αρχική	        τελική	     μήνες
10/5/2014	23/8/2015	3
10/5/2014	23/9/2015	4
10/5/2014	10/5/2015	0
10/5/2014	20/5/2015	0
10/5/2014	1/2/2015	8

Απολύτως ισοδύναμοι είναι οι παρακάτω τύποι:

=MOD(12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)+IF(DAY(B1)<DAY(A1);-1;0);12)

=12*(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)))-MONTH(A1)+MONTH(B1)-(DAY(B1)<DAY(A1))

=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));12-MONTH(A1)+MONTH(B1)-(DAY(B1)<DAY(A1));MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1)))

Όπως είναι φυσικό, οι τύποι αυτοί επιστρέφουν έναν ακέραιο αριθμό μεταξύ 0 και 11.

(3) Το όρισμα ‘μονάδα’ είναι md
Η συνάρτηση:

=DATEDIF(A1;B1;"md")

επιστρέφει τις υπόλοιπες ημέρες (της διαφοράς Β1-Α1)που απομένουν, αφού αφαιρέσει όλες τις ημέρες που αντιστοιχούν σε ολόκληρα έτη και ολόκληρους μήνες. Παραδείγματα:

αρχική	        τελική	        ημέρες
9/3/2014	20/5/2015	11
9/3/2014	16/9/2015	7
9/3/2014	31/5/2015	22
9/3/2014	9/3/2015	0
1/3/2014	31/1/2015	30

Στην περίπτωση αυτή όμως η DATEDIF παρουσιάζει δύο προβλήματα: Πρώτον έχουν αναφερθεί πολλές περιπτώσεις, όπου η συνάρτηση επιστρέφει προφανώς λάθος αποτέλεσμα. (πχ για Α1= 12/7/2007 και για Β1=4/1/2008, επιστρέφει 136). Τα λάθη αυτά ποικίλουν μεταξύ των εκδόσεων του excel και των υπολογιστών που τρέχουν. (II)
Δεύτερον, πιθανά να μην συμφωνείτε με τον τρόπο που κάνει η DATEDIF τον υπολογισμό. Τα παρακάτω αποτελέσματα:

αρχική	        τελική	        ημέρες
31/1/2015       1/2/2016	-1
30/11/2014	1/12/2015	2
29/2/2016	1/3/2017	3
31/1/2015	11/4/2015 	10 
27/2/2003	7/2/2005	11
31/3/2015	1/4/2015        0
29/2/2000	1/3/2000	3

είτε είναι λάθη του συστήματος, είτε προκύπτουν γιατί με αυτόν τον τρόπο κάνει η DATEDIF τον υπολογισμό, πιστεύω θα συμφωνείτε και εσείς ότι δεν είναι αποδεκτά αποτελέσματα. Στην επίσημη βοήθεια για την συνάρτηση δεν υπάρχει καμία αναφορά για τον τρόπο που ορίζει την διαφορά η DATEDIF. Πριν καταλήξουμε σε έναν αποδεκτό εναλλακτικό τύπο, νομίζω είναι απαραίτητο να συζητήσουμε για τον τρόπο που θα κάνουμε τον υπολογισμό. Διακρίνουμε δύο περιπτώσεις:
α) Όταν η ημέρα του μήνα της αρχικής ημερομηνίας είναι μικρότερη ή ίση από την ημέρα του μήνα της τελικής ημερομηνίας. Με όρους του excel, όταν DAY(A1) <=DAY(B1). Τότε οι υπόλοιπες ημέρες είναι η απλή διαφορά την ημερομηνιών : DAY(B1)-DAY(A1).

αρχική          τελική	        ημέρες
8/1/2011	24/12/2015	16 = 24-8
7/7/2000	17/1/2016	10 = 17-7
7/2/1998	23/12/2000	16 = 23-7
26/8/2001	26/11/2001	0 = 26-26
14/12/2000	15/9/2014	1 = 15-14

Στην περίπτωση αυτή και η DATEDIF με τον ίδιο τρόπο υπολογίζει την διαφορά.
β) Όταν η ημέρα του μήνα της αρχικής ημερομηνίας είναι μεγαλύτερη από την ημέρα του μήνα της τελικής ημερομηνίας. Δηλαδή, όταν DAY(A1) >DAY(B1). . Στην περίπτωση αυτή υπάρχουν πολλοί τρόποι υπολογισμού. Δεν θα σας κουράσω αναφέροντάς τους εδώ. (ΙΙΙ) Θα περιγράψω μόνο αυτόν που τελικά προτείνει το ιστολόγιο: Από το σύνολο των ημερών, (από την αρχική ημερομηνία Α1 έως την τελική Β1), αφαιρούνται οι ημέρες που αντιστοιχούν στους ολόκληρους μήνες που μεσολαβούν μεταξύ των ημερομηνιών και η διαφορά είναι το αποτέλεσμα της συνάρτησης . Παράδειγμα: Αν Α1=25/7/2014 και Β1=10/4/2015,τότε από Α1 έως Β1 οι ημέρες είναι =Β1-Α1=259 ημέρες, και το σύνολο των ημερών στους ακέραιους μήνες που μεσολαβούν, δηλαδή από 1/8/2014 έως και 31/3/2015 είναι 243 ημέρες. Η διαφορά 259-243=16 είναι η τιμή που αναζητάμε.
Την πρώτη ημέρα του πρώτου μήνα που μεσολαβεί, δίνει ο τύπος:
=DATE(YEAR(A1);MONTH(A1)+1;1)
και την τελευταία ημέρα του τελευταίου μήνα που μεσολαβεί, δίνει ο τύπος:
=DATE(YEAR(B1);MONTH(B1);1)-1

Μαζεύοντας όλα τα παραπάνω που είπαμε, σε μία έκφραση, προκύπτει ο τελικός τύπος:

=IF(DAY(B1)>=DAY(A1);DAY(B1)-DAY(A1);B1-A1-(DATE(YEAR(B1);MONTH(B1);1)-DATE(YEAR(A1);MONTH(A1)+1;1)))

ή σε μία ισοδύναμη αλλά πιο σύντομη διατύπωση:

=DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)

Επαναλαμβάνω ότι οι δύο παραπάνω τύποι δύνονται σαν εναλλακτικοί (αλλά που επιστρέφουν σωστότερα αποτελέσματα) του τύπου:

=DATEDIF(A1;B1;"md")

Στο excel βιβλίο datedif_tests.xls (που μπορείτε να κατεβάσετε από εδώ ή από εδώ, μια μακροεντολή παράγει τυχαίες ημερομηνίες και μπορείτε να δείτε στην πράξη τις διαφορές μεταξύ της DATEDIF με όρισμα md και αυτής που προτείνει το ιστολόγιο, και να τις αξιολογήσετε.

Βάζοντας τα όλα μαζί : έτη-μήνες- ημέρες
Συνδυάζοντας τύπους από τις παραπάνω ενότητες μπορείτε να εκφράσετε τη διαφορά δύο ημερομηνιών στη μορφή έτη-μήνες- ημέρες.

=DATEDIF(A1;B1;"y") &" έτη, "& DATEDIF(A1;B1;"ym")& " μήνες, "&DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)&" ημέρες"

ή

=DATEDIF(A1;B1;"y") &"-"& DATEDIF(A1;B1;"ym")& "-"&DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)

Ο πρώτος για το χρονικό διάστημα από 11/12/2013 έως 7/6/2015 επιστρέφει: 1 έτη, 5 μήνες, 27 ημέρες και ο δεύτερος επιστρέφει: 1-5-27. Σημειώστε ότι σε αυτή τη σύνταξη, οι συναρτήσεις επιστρέφουν κείμενο και όχι αριθμούς.

(4) Το όρισμα ‘μονάδα’ είναι m
Η συνάρτηση:

=DATEDIF(A1;B1;"m")

επιστρέφει τον συνολικό χρόνο της διαφορά των ημερομηνιών σε ακέραιους μήνες. Το τυχόν υπόλοιπο σε ημέρες αγνοείται. Παραδείγματα:

αρχική	        τελική	        μήνες
19/12/2000	19/2/2004	38
6/3/1950	30/5/2015	782
20/8/1990	28/8/2000	120
1/3/2015	31/3/2015	0

Όπως και στις περιπτώσεις (1) και (2) η DATEDIF με όρισμα m, επιστρέφει απολύτως σωστά αποτελέσματα. Μια ισοδύναμη συνάρτηση φύλλου είναι η:

=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

(5) Το όρισμα ‘μονάδα’ είναι d
Η συνάρτηση:

=DATEDIF(A1;B1;"d")

επιστρέφει τον συνολικό χρόνο της διαφορά των ημερομηνιών σε ημέρες.
Αν και η συνάρτηση επιστρέφει απολύτως σωστά αποτελέσματα, δεν έχετε κανένα λόγο να την χρησιμοποιήσετε μία και τα ίδια αποτελέσματα θα πάρετε από την απλή αφαίρεση:

=B1-A1

(6) Το όρισμα ‘μονάδα’ είναι yd
Η συνάρτηση:

=DATEDIF(A1;B1;"yd")

επιστρέφει τις υπόλοιπες ημέρες (της διαφοράς Β1-Α1)που απομένουν, αφού αφαιρέσει όλες τις ημέρες που αντιστοιχούν σε ολόκληρα έτη. Παραδείγματα:

αρχική	        τελική	        ημέρες
30/5/2010	30/5/2015	0
30/5/2010	31/5/2015       1
17/4/2012	23/5/2014	36
1/1/2015	31/12/2015	364

Δυστυχώς και σε αυτή την περίπτωση η συνάρτηση σε πολλές περιπτώσεις δεν επιστρέφει σωστά αποτελέσματα. Αν διαπιστώσετε κάτι τέτοιο στον υπολογιστή σας ή το βιβλίο σας θα ανοίγει από διάφορους υπολογιστές, όπου δεν μπορείτε να ελέγξετε τη συμπεριφορά της, τότε πιο καλά χρησιμοποιείστε την παρακάτω τύπο:

=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));B1-DATE(YEAR(B1)-1;MONTH(A1);DAY(A1));B1-(DATE(YEAR(B1);MONTH(A1);DAY(A1))))

ή σε ποιο σύντομη διατύπωση:

=B1-DATE(YEAR(B1)-(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)));MONTH(A1);DAY(A1))

. . . . . . . . . . .
(Ι)
Τα ορίσματα αρχική και τελική ημερομηνία εκτός από διευθύνσεις κελιών που περιέχουν ημερομηνίες ή ημερομηνίες σαν κείμενο, μπορούν να είναι συναρτήσεις που επιστρέφουν ημερομηνίες όπως TODAY, DATEVALUE, DATE κλπ. Ακόμα οι ημερομηνίες μπορούν να είναι γραμμένες απ ευθείας μέσα στις συναρτήσεις στη μορφή ημέρα/μήνας/έτος κλεισμένες απαραίτητα σε εισαγωγικά. Παράδειγμα:

=DATEDIF("12/3/2012";"11/1/2015";"y")

Αν στα settings του υπολογιστή σας έχετε ορίσει διαφορετική μορφή αναγραφής ημερομηνιών, προσαρμόστε ανάλογα την μορφή των ημερομηνιών που εισάγονται σαν κείμενο.
Αν η αρχική ημερομηνία είναι μεγαλύτερη από την τελική, οι συναρτήσεις θα επιστρέψουν μία τιμή σφάλματος.
(ΙΙ)
Πέρυσι το καλοκαίρι έγινε στο ιστολόγιο μια μεγάλη συζήτηση και αναφέρθηκαν πολλά τέτοια λάθη. Μπορείτε να δείτε τη συζήτηση στη σελίδα about από 12/7/14 έως 31/7/14 ή να κατεβάσετε το αρχείο about_datedif_comments.pdf, από εδώ ή από εδώ, όπου μάζεψα όλη την ενδιαφέρουσα αυτή συζήτηση και τις λύσεις που προτάθηκαν.
(ΙΙΙ)
Από εδώ ή από εδώ μπορείτε να κατεβάσετε όλο το παραπάνω σημείωμα σε μορφή pdf. Εκεί θα βρείτε επί πλέον τρόπους υπολογισμού για την περίπτωση αυτή, όπου το όρισμα μονάδα είναι md.

DimitrisHorn1

This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , , , . Bookmark the permalink.