Ένας χρόνος, ένας μήνας, μια ημέρα…. Η συνάρτηση 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

Posted in excel, Συναρτήσεις φύλλου | Tagged , , , ,

Η Ελληνίδα NETWORKDAYS

Για τη μέτρηση των εργάσιμων ημερών σε ένα χρονικό διάστημα, το excel διαθέτει την συνάρτηση NETWORKDAYS. Η συνάρτηση είναι μόνιμο μέλος της συλλογής συναρτήσεων του excel από την έκδοση 2007 και μετά. Στις παλιότερες εκδόσεις θα τη βρείτε αν έχετε ενεργοποιημένο το πρόσθετο ‘Πακέτο Εργαλείο Ανάλυσης’. Μια βελτιωμένη έκδοση της συνάρτησης, η NETWORKDAYS.INTL εμφανίστηκε στην έκδοση 2010. Περιγραφή για τη σύνταξή και τον τρόπο χρήσης των συναρτήσεων αυτών, μπορείτε να βρείτε στην επίσημη βοήθεια για το excel, εδώ για την NETWORKDAYS και εδώ για την NETWORKDAYS.INTL

.
Η συνάρτηση WorkingDays
Επειδή εδώ είμαστε ένα ιστολόγιο για τον έλληνα χρήστη του excel, θα ντύσουμε τη συνάρτηση NETWORKDAYS με ελληνική φορεσιά, ή καλλίτερα θα ράψουμε μια εξ ολοκλήρου καινούργια φορεσιά, με τα πιο απλά υλικά της vba, ώστε η συνάρτηση να είναι συμβατή με όλες τις εκδόσεις του excel και όσο γίνεται απλή στη χρήση. Η συνάρτησή έχει το… ελληνικότατο όνομα WorkingDays και θεώρει εξ ορισμού αργίες, όλα τα Σάββατα, όλες τις Κυριακές και τις παρακάτω 13 ημερομηνίες:
— Πρωτοχρονιά (1 Ιανουαρίου)
— Θεοφάνεια (6 Ιανουαρίου)
— Καθαρή Δευτέρα (κινητή)
— Εθνική Γιορτή (25 Μαρτίου)
— Μεγάλη Παρασκευή (κινητή)
— Άγιο Πάσχα (κινητή)
— Δευτέρα Διακαινησίμου (2η του Πάσχα, κινητή)
— Πρωτομαγιά (1 Μαΐου)
— Αγίου Πνεύματος (κινητή)
— Κοίμηση Θεοτόκου (15 Αυγούστου)
— Εθνική Γιορτή (28 Οκτωβρίου)
— Χριστούγεννα (25 Δεκεμβρίου)
— Σύναξη Θεοτόκου (26 Δεκεμβρίου)
Η πλήρης σύνταξη τη συνάρτησης είναι:
=WorkingDays(start_date;end_date;extra_holidays)
δηλαδή, δέχεται τρία ορίσματα: start_date, end_date είναι αντίστοιχα η πρώτη και ή τελευταία ημερομηνία του χρονικού διαστήματος του οποίου ζητάτε το πλήθος των εργάσιμων ημερών, και το προαιρετικό όρισμα extra_holidays με το οποίο μπορείτε να χαρακτηρίσετε σαν αργίες, όσες και όποιες άλλες ημερομηνίες επί πλέον των παραπάνω θέλετε.
Παράδειγμα: Αν στο κελί Α1 είναι η ημερομηνία 1/10/2015, και στο κελί Β1 η 31/10/2015, τότε η συνάρτηση =WorkingDays(A1;B1) θα επιστρέψει 21. Δηλαδή από το σύνολο των 31 ημερών του Οκτωβρίου αφαίρεσε τα Σαββατοκύριακα και την 28η Οκτωβρίου. Αν όμως ζείτε στη Θεσσαλονίκη και για σας είναι αργία και η 26η Οκτωβρίου, γιορτή του Αγίου Δημητρίου, θα γράψετε σε ένα κελί έστω το C1: 26/10/2015 και θα συντάξετε τη συνάρτηση έτσι: =WorkingDays(A1;B1;C1), η οποία θα επιστρέψει σωστά 20.
Οι ημερομηνίες start_date, end_date και extra_holidays μπορούν να δοθούν:
— από κελιά στα οποία είναι γραμμένες σαν κανονικές ημερομηνίες του excel
— από κελιά στα οποία είναι γραμμένες σαν κείμενο
— απ ευθείας μέσα στην συνάρτηση (απαραίτητα κλεισμένες σε εισαγωγικά)
— με την βοήθεια της συνάρτηση DATE
— σαν σειριακοί αριθμοί που αντιπροσωπεύουν τις ημερομηνίες
— σαν συναρτήσεις που επιστρέφουν ημερομηνίες
— με οποιοδήποτε συνδυασμό των παραπάνω
Ειδικά στο όρισμα end_date μπορεί να μη του δοθεί τιμή ή να του δοθεί τιμή μηδέν. Τότε η συνάρτηση επιστρέφει το πλήθος των εργασίμων ημερών από την ημέρα start_date έως το τέλος του μήνα.
Αν οι extra_holidays ημερομηνίες είναι περισσότερες από μία, γράψτε τις σε διαδοχικά κελιά, οριζόντια ή κάθετα ή γράψετε τις κατευθείαν στην συνάρτηση περικλείοντας σε αγκύλες { }

.
Παραδείγματα:
Αν στο κελί Α1 είναι η ημερομηνία 1/10/2015 και στο κελί Β1 η 31/10/2015, όλες οι παρακάτω συναρτήσεις επιστρέφουν τις εργάσιμες ημέρες του Οκτωβρίου 2015

=WorkingDays(A1;B1)
=WorkingDays(A1;0)
=WorkingDays(A1)
=WorkingDays("1/10/2015";"31/10/2015")
=WorkingDays("1/10/2015")
=WorkingDays(DATE(2015;10;1);DATE(2015;10;31))
=WorkingDays(DATE(2015;10;1))
=WorkingDays(42278;42308) ‘Σειριακοί αριθμοί ημερομηνιών
=WorkingDays(42278)

Οι παρακάτω συναρτήσεις επιστρέφουν το πλήθος των εργάσιμων ημερών του Οκτωβρίου 2015, υπολογίζοντας σαν αργία και την γιορτή του Αγίου Δημητρίου που έχει γραφτεί στο κελί C1: 26/10/2015

=WorkingDays(A1;B1;C1)
=WorkingDays(A1;0;C1)
=WorkingDays("1/10/2015";"31/10/15";"26/10/2015")
=WorkingDays("1/10/2015";0;"26/10/2015")
=WorkingDays(DATE(2015;10;1);;DATE(2015;10;26))

Για να μετρήσετε τις εργάσιμες ημέρες του πρώτου εξαμήνου του 2015 θεωρώντας επί πλέον αργίες τις 3 πρώτες ημέρες του Φεβρουαρίου:

Κελί Α1: 1/1/2015
Κελί Β1: 30/6/2015
Κελιά C1, C2, C3: 1/2/15, 2/2/15, 3/2/15
=WorkingDays(A1;B1;C1:C3)

Ο ίδιος υπολογισμός, γράφοντας τις ημερομηνίες κατ ευθείαν μέσα στη συνάρτηση (Παρατηρήστε ότι οι τιμές του ορίσματος extra_holidays στην περίπτωση που είναι περισσότερες από μία εισάγονται σαν πίνακας, με αγκύλες { } ) :

=WorkingDays("1/1/15";"30/6/15";{"2/2/15";"3/2/15";"4/2/15"})

Πλήθος εργασίμων ημερών από την αρχή του τρέχοντος χρόνου έως σήμερα και από αύριο έως το τέλος του τρέχοντος έτους:

=WorkingDays(DATE(YEAR(TODAY());1;1);TODAY())
=WorkingDays(TODAY()+1;DATE(YEAR(TODAY());12;31))

Περισσότερα παραδείγματα θα βρείτε στο βιβλίο WorkingDaysΕxamples.xls το οποίο μπορείτε να κατεβάσετε από εδώ ή από εδώ.
Όλα τα παραδείγματα έχουν γραφτεί θεωρώντας ότι στα setting του υπολογιστή σας οι ημερομηνίες έχουν ρυθμιστεί να έχουν μορφή: ημέρα/μήνας/έτος. Διαφορετικά προσαρμόστε ανάλογα την γραφή των ημερομηνιών. Αν το βιβλίο σας που θα περιέχει την WorkingDays (ή την NETWORKDAYS) θα ανοίγει από διάφορους υπολογιστές για τους οποίους δεν γνωρίζετε τις ρυθμίσεις ημερομηνιών, τότε χρησιμοποιείστε τη συνάρτηση DATE για να εισάγετε τα ορίσματα. Αν χρησιμοποιήσετε τη συνάρτηση για ημερομηνίες παρελθόντων ετών, βεβαιωθείτε ότι ίσχυαν τότε οι αργίες που υπολογίζει. Η πιο παλιά ημερομηνία που επιτρέπει η συνάρτηση είναι η 1/1/1950.

.
Η συνάρτηση WorkingDaysPlus
Επειδή ήδη μερικοί διαμαρτύρεστε ότι δεν έχουν όλοι οι εργαζόμενοι το σταθερό αυτό πακέτο αργιών, και η συνάρτηση δεν φροντίζει για αυτούς, το ιστολόγιο μαζί με τη γιορτινή φορεσιά, έραψε και μία φόρμα εργασίας, την συνάρτηση WorkingDaysPlus, που δεν θεωρεί καμία ημέρα αργία. Εξ ορισμού και τις 365 (ή 366) ημέρες του χρόνου τις θεώρει εργάσιμες. Η συνάρτηση WorkingDaysPlus εκτός των τριών ορισμάτων start_date, end_date και extra_holidays δέχεται 20 ακόμα (προαιρετικά) ορίσματα. Με τα 7 πρώτα από αυτά ορίζετε ποίες από τις ημέρες της εβδομάδας είναι αργίες . Δηλώστε τις αργίες με τον αριθμό 1 ή με TRUE και τις εργάσιμες με 0 (μηδέν) ή FALSE ή αφήστε το όρισμα κενό. Στην παρακάτω εικόνα έχει δηλωθεί αργία μόνο η Κυριακή:

WorkingDays11
Με τα υπόλοιπα 13 ορίσματα καθορίζετε ποιες είναι αργίες και ποιές όχι από τις γιορτές του παραπάνω πίνακα. Στην παρακάτω εικόνα οι Μεγάλη Παρασκευή και του Αγίου Πνεύματος έχουν δηλωθεί εργάσιμες και οι υπόλοιπες 11 αργίες:

WorkingDays22
Άλλες ημερομηνίες που θέλετε να ορίσετε σαν αργίες, θα τις γράψετε στο όρισμα extra_holidays. Για το όρισμα αυτό και για τα ορίσματα start_date και end_date, ισχύουν όσα είπαμε για την συνάρτηση WorkingDays. Η πιο παλιά ημερομηνία που επιτρέπει η συνάρτηση WorkingDaysPlus είναι η 1/1/1924.

.
Ο κώδικας για την WorkingDays:

Function WorkingDays(start_date As Date, Optional end_date As Date = 0, _
            Optional extra_holidays As Variant) As Long
'Από το Excel Λύσεις 2015
Application.Volatile
Dim pl As Long, i As Long
Dim etos As Integer, h As Integer
Dim V() As Date
Dim VH As Variant
Dim item  As Variant
Dim fd As Date, easter As Date
start_date = DateValue(start_date)
If end_date = 0 Then
    end_date = DateSerial(Year(start_date), Month(start_date) + 1, 1) - 1
Else
end_date = DateValue(end_date)
End If
If start_date < DateSerial(1949, 12, 31) Then _
    WorkingDays = CVErr(xlErrValue): GoTo telos
fd = DateSerial(1904, 1, 1)
pl = end_date - start_date
ReDim V(pl)
For i = 0 To pl
    V(i) = start_date + i
Next
For i = 0 To pl
    If DatePart("w", V(i), 1) = 1 Then V(i) = fd
    If DatePart("w", V(i), 1) = 7 Then V(i) = fd
Next
For etos = Year(start_date) To Year(end_date)
    easter = DateSerial(etos, 3, 31) + ((19 * (etos Mod 19) + 16) Mod 30) + ((2 * (etos Mod 4) + _
                        4 * (etos Mod 7) + 6 * ((19 * (etos Mod 19) + 16) Mod 30)) Mod 7) + 3
    VH = Array(DateSerial(etos, 1, 1), DateSerial(etos, 1, 6), DateSerial(etos, 3, 25), DateSerial(etos, 5, 1), _
                        DateSerial(etos, 8, 15), DateSerial(etos, 10, 28), DateSerial(etos, 12, 25), _
                        DateSerial(etos, 12, 26), easter - 48, easter - 2, easter + 1, easter + 50)
    For i = 0 To pl
        For h = 0 To 11
            If V(i) = VH(h) Then V(i) = fd
        Next h
    Next i
Next etos
If Not IsMissing(extra_holidays) Then
   If TypeName(extra_holidays) = "String" Or _
        TypeName(extra_holidays) = "Double" _
        Then extra_holidays = Array(extra_holidays)
    For Each item In extra_holidays
    For i = 0 To pl
    If V(i) = item Then V(i) = fd
    Next i
Next item
End If
For i = 0 To pl
If V(i) > fd Then WorkingDays = WorkingDays + 1
Next
telos:
End Function

.

Ο κώδικας για την WorkingDaysPlus:

Function WorkingDaysPlus(start_date As Date, Optional end_date As Date = 0, _
        Optional extra_holidays As Variant, _
        Optional DEYTERA As Boolean = False, _
        Optional TRITH As Boolean = False, _
        Optional TETARTH As Boolean = False, _
        Optional PEMPTH As Boolean = False, _
        Optional PARASKEYH As Boolean = False, _
        Optional SABBATO As Boolean = False, _
        Optional KYRIAKH As Boolean = False, _
        Optional IAN_01 As Boolean = False, _
        Optional IAN_06 As Boolean = False, _
        Optional KATHARH_DEYTERA As Boolean = False, _
        Optional MAR_25 As Boolean = False, _
        Optional MEGALH_PARASKEYH As Boolean = False, _
        Optional PASXA As Boolean = False, _
        Optional DEYTERA_PASXA As Boolean = False, _
        Optional MAI_01 As Boolean = False, _
        Optional AGIOY_PNEYMATOS As Boolean = False, _
        Optional AYG_15 As Boolean = False, _
        Optional OKT_28 As Boolean = False, _
        Optional DEK_25 As Boolean = False, _
        Optional DEK_26 As Boolean = False) As Long
'Από το Excel Λύσεις, 2015
Application.Volatile
Dim pl As Long, i As Long
Dim etos As Integer, h As Integer
Dim vday As Integer
Dim V() As Date
Dim VH As Variant
Dim item As Variant
Dim fd As Date, easter As Date
start_date = DateValue(start_date)
If end_date = 0 Then
    end_date = DateSerial(Year(start_date), Month(start_date) + 1, 1) - 1
Else
end_date = DateValue(end_date)
End If
If start_date < DateSerial(1924, 1, 1) Then _
    WorkingDaysPlus = CVErr(xlErrValue): GoTo telos
fd = DateSerial(1904, 1, 1)
pl = end_date - start_date
ReDim V(pl)
For i = 0 To pl
    V(i) = start_date + i
Next
For i = 0 To pl
vday = DatePart("w", V(i), 1)

    If DEYTERA = True Then
    If vday = 2 Then V(i) = fd
    End If

    If TRITH = True Then
    If vday = 3 Then V(i) = fd
    End If

    If TETARTH = True Then
    If vday = 4 Then V(i) = fd
    End If

    If PEMPTH = True Then
    If vday = 5 Then V(i) = fd
    End If

    If PARASKEYH = True Then
    If vday = 6 Then V(i) = fd
    End If

    If SABBATO = True Then
    If vday = 7 Then V(i) = fd
    End If

    If KYRIAKH = True Then
    If vday = 1 Then V(i) = fd
    End If
Next
For etos = Year(start_date) To Year(end_date)
    easter = DateSerial(etos, 3, 31) + ((19 * (etos Mod 19) + 16) Mod 30) + ((2 * (etos Mod 4) + _
                        4 * (etos Mod 7) + 6 * ((19 * (etos Mod 19) + 16) Mod 30)) Mod 7) + 3
    VH = Array(DateSerial(etos, 1, 1), DateSerial(etos, 1, 6), DateSerial(etos, 3, 25), DateSerial(etos, 5, 1), _
                        DateSerial(etos, 8, 15), DateSerial(etos, 10, 28), DateSerial(etos, 12, 25), _
                        DateSerial(etos, 12, 26), easter - 48, easter - 2, easter, easter + 1, easter + 50)
    If IAN_01 = False Then VH(0) = ""
    If IAN_06 = False Then VH(1) = ""
    If MAR_25 = False Then VH(2) = ""
    If MAI_01 = False Then VH(3) = ""
    If AYG_15 = False Then VH(4) = ""
    If OKT_28 = False Then VH(5) = ""
    If DEK_25 = False Then VH(6) = ""
    If DEK_26 = False Then VH(7) = ""
    If KATHARH_DEYTERA = False Then VH(8) = ""
    If MEGALH_PARASKEYH = False Then VH(9) = ""
    If PASXA = False Then VH(10) = ""
    If DEYTERA_PASXA = False Then VH(11) = ""
    If AGIOY_PNEYMATOS = False Then VH(12) = ""
    For i = 0 To pl
        For h = 0 To 12
            If V(i) = VH(h) Then V(i) = fd
        Next h
    Next i
Next etos
If Not IsMissing(extra_holidays) Then
    If TypeName(extra_holidays) = "String" Or _
        TypeName(extra_holidays) = "Double" _
        Then extra_holidays = Array(extra_holidays)
    For Each item In extra_holidays
    For i = 0 To pl
    If V(i) = item Then V(i) = fd
    Next i
Next item
End If
For i = 0 To pl
If V(i) > fd Then WorkingDaysPlus = WorkingDaysPlus + 1
Next
telos:
End Function
Posted in excel, Συναρτήσεις Χρήστη, VBA | Tagged , , , , , , ,