Γενική φόρμουλα
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Περίληψη
Για να υπολογίσετε τις συνολικές ώρες εργασίας μεταξύ δύο ημερομηνιών και ωρών, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στη συνάρτηση NETWORKDAYS. Στο παράδειγμα που εμφανίζεται, το E5 περιέχει αυτόν τον τύπο:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
όπου "κατώτερο" είναι το ονομαζόμενο εύρος H5 και "άνω" είναι το ονομαζόμενο εύρος H6.
Σημείωση: αυτό το παράδειγμα εμπνεύστηκε από μια φόρμουλα πρόκληση στο Chandoo και από μια πιο ολοκληρωμένη λύση που παρέχεται από τον κύριο τύπο Barry Houdini στο φόρουμ MrExcel.
Εξήγηση
Αυτός ο τύπος υπολογίζει τις συνολικές ώρες εργασίας μεταξύ δύο ημερομηνιών και ωρών, που συμβαίνουν μεταξύ "χαμηλότερης" και "ανώτερης" ώρας. Στο παράδειγμα που εμφανίζεται, ο χαμηλότερος χρόνος είναι 9:00 π.μ. και ο ανώτερος χρόνος είναι 5:00 μ.μ. Αυτά εμφανίζονται στον τύπο ως τα ονομαστικά εύρη "κάτω" και "άνω".
Η λογική του τύπου είναι να υπολογίσετε όλες τις πιθανές ώρες εργασίας μεταξύ των ημερομηνιών έναρξης και λήξης, συμπεριλαμβανομένων και στη συνέχεια να αποσυρθείτε τυχόν ώρες κατά την ημερομηνία έναρξης που συμβαίνει μεταξύ της ώρας έναρξης και της χαμηλότερης ώρας, καθώς και οποιωνδήποτε ωρών κατά την ημερομηνία λήξης που πραγματοποιείται μεταξύ την ώρα λήξης και την ανώτερη ώρα.
Η συνάρτηση NETWORKDAYS χειρίζεται τον αποκλεισμό των Σαββατοκύριακων και των αργιών (όταν παρέχεται ως εύρος ημερομηνιών). Μπορείτε να μεταβείτε στο NETWORKDAYS.INTL εάν το πρόγραμμά σας έχει μη τυπικές εργάσιμες ημέρες.
Μορφοποίηση εξόδου
Το αποτέλεσμα είναι ένας αριθμός που αντιπροσωπεύει τις συνολικές ώρες. Όπως όλες οι ώρες του Excel, θα πρέπει να μορφοποιήσετε την έξοδο με κατάλληλη μορφή αριθμού. Στο παράδειγμα που εμφανίζεται, χρησιμοποιούμε:
(h):mm
Οι αγκύλες τετράγωνου εμποδίζουν την περιστροφή του Excel όταν οι ώρες είναι μεγαλύτερες από 24. Με άλλα λόγια, καθιστούν δυνατή την εμφάνιση ωρών μεγαλύτερων από 24. Εάν χρειάζεστε δεκαδική τιμή για ώρες, μπορείτε να πολλαπλασιάσετε το αποτέλεσμα με 24 και να μορφοποιήσετε ως κανονικός αριθμός.
Απλή έκδοση
Εάν οι ώρες έναρξης και λήξης εμφανίζονται πάντα μεταξύ χαμηλότερων και ανώτερων χρόνων, μπορείτε να χρησιμοποιήσετε μια απλούστερη έκδοση αυτού του τύπου:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Χωρίς ώρα έναρξης και ώρα λήξης
Για να υπολογίσετε τις συνολικές ώρες εργασίας μεταξύ δύο ημερομηνιών, υποθέτοντας ότι όλες οι ημέρες είναι πλήρεις εργάσιμες ημέρες, μπορείτε να χρησιμοποιήσετε έναν ακόμη απλούστερο τύπο:
=NETWORKDAYS(start,end,holidays)*hours
Δείτε εξηγήσεις εδώ για λεπτομέρειες.
