εξαιτίαςΟι ημερομηνίες και οι ώρες αποθηκεύονται ως αριθμοί στο backend του Excel, ώστε να μπορείτε εύκολα να χρησιμοποιείτε απλές αριθμητικές πράξεις και τύπους σε τιμές ημερομηνίας και ώρας.
Για παράδειγμα, μπορείτε να προσθέσετε και να αφαιρέσετε δύο διαφορετικές τιμές ώρας ή τιμές ημερομηνίας ή μπορείτε να υπολογίσετε τη διαφορά ώρας μεταξύ δύο δεδομένων ημερομηνιών/ωρών.
Σε αυτό το σεμινάριο θα σας δείξω πώς να το κάνετε Εκτελέστε υπολογισμούς χρησιμοποιώντας το χρόνο στο Excelδιάφορες μέθοδοι (όπως υπολογισμός χρονικής διαφοράς, πρόσθεση και αφαίρεση χρόνου, εμφάνιση χρόνου σε διαφορετικές μορφές και υπολογισμός του αθροίσματος των τιμών χρόνου).
Περιεχόμενα
- 1 Πώς χειρίζεται το Excel ημερομηνίες και ώρες;
- 2 Ο τύπος για τον υπολογισμό της χρονικής διαφοράς μεταξύ δύο φορές
- 2.1 Απλή αφαίρεση για τον υπολογισμό της διαφοράς χρόνου στο Excel
- 2.2 Υπολογίστε τη διαφορά ώρας σε ώρες, λεπτά ή δευτερόλεπτα
- 2.3 Υπολογίστε τη διαφορά ώρας χρησιμοποιώντας τη λειτουργία TEXT
- 2.4 Λάβετε τη διαφορά ώρας σε μία μονάδα (ώρες/λεπτά), αγνοώντας άλλες
- 2.5 Υπολογίστε το χρόνο που πέρασε τώρα (από την ώρα έναρξης)
- 3 Υπολογισμός της ώρας κατά την αλλαγή της ημερομηνίας (υπολογισμός και εμφάνιση αρνητικής ώρας στο Excel)
- 4 Προσθήκη και αφαίρεση χρόνου στο Excel
- 5 Πώς να αθροίσετε το χρόνο στο Excel
- 6 Τα αποτελέσματα εμφανίζουν κατακερματισμό (###) αντί για ημερομηνία/ώρα (αιτία + επιδιόρθωση)
- 7 Συχνές ερωτήσεις σχετικά με τον υπολογισμό της πρόσθεσης και της αφαίρεσης χρόνου στο Excel
- 8 Ω, γεια, χαίρομαι που σε γνώρισα.
Πώς χειρίζεται το Excel ημερομηνίες και ώρες;
Όπως ανέφερα, οι ημερομηνίες και οι ώρες αποθηκεύονται ως αριθμοί σε κελιά στο Excel.Ο ακέραιος αντιπροσωπεύει ολόκληρη την ημέρα, το κλασματικό μέρος του αριθμού αντιπροσωπεύει το μέρος της ημέρας (μπορεί να μετατραπεί σε τιμές ωρών, λεπτών και δευτερολέπτων)
Για παράδειγμα, η τιμή 1 αντιπροσωπεύει την 1900η Ιανουαρίου 1 στο Excel, το οποίο είναι το σημείο εκκίνησης για να αρχίσει το Excel να εξετάζει ημερομηνίες.
Άρα 2 σημαίνει 1990 Ιανουαρίου 1, 2 σημαίνει 3 Ιανουαρίου 1900 και ούτω καθεξής, 1 σημαίνει 3 Ιανουαρίου 44197.
Σημείωση: Το Excel για Windows και το Excel για Mac ακολουθούν διαφορετικές ημερομηνίες έναρξης.1 στο Excel για Windows σημαίνει 1900 Ιανουαρίου 1, 1 στο Excel για Mac σημαίνει 1 Ιανουαρίου 1904
Εάν αυτοί οι αριθμοί έχουν ψηφία μετά την υποδιαστολή, το Excel αντιμετωπίζει αυτούς τους αριθμούς ως μέρος μιας ημέρας και μπορεί να τους μετατρέψει σε ώρες, λεπτά και δευτερόλεπτα.
Για παράδειγμα, το 44197.5 σημαίνει την 2021η Ιανουαρίου 1 στις 1:12:00 μ.μ.
Έτσι, εάν εργάζεστε με τιμές χρόνου στο Excel, βασικά εργάζεστε με το κλασματικό μέρος του αριθμού.
Το Excel σάς δίνει την ευελιξία να μετατρέπετε κλασματικά μέρη σε διαφορετικές μορφές, όπως μόνο ώρες, μόνο λεπτά, μόνο δευτερόλεπτα ή συνδυασμό ωρών, λεπτών και δευτερολέπτων
Τώρα που καταλαβαίνετε πώς αποθηκεύεται ο χρόνος στο Excel, ας δούμε μερικά παραδείγματα για τον τρόπο υπολογισμού της διαφοράς ώρας μεταξύ δύο διαφορετικών ημερομηνιών ή ωρών στο Excel
Ο τύπος για τον υπολογισμό της χρονικής διαφοράς μεταξύ δύο φορές
Σε πολλές περιπτώσεις, το μόνο που χρειάζεται να κάνετε είναι να μάθετε τον συνολικό χρόνο που έχει παρέλθει μεταξύ δύο χρονικών τιμών (για παράδειγμα, στην περίπτωση ενός χρονοδιαγράμματος με In-time και Out-time).
Η μέθοδος που επιλέγετε εξαρτάται από το πώς αναφέρεται η ώρα στο κελί και πώς θέλετε να μορφοποιηθεί το αποτέλεσμα.
Ας δούμε μερικά παραδείγματα
Απλή αφαίρεση για τον υπολογισμό της διαφοράς χρόνου στο Excel
Δεδομένου ότι οι χρόνοι αποθηκεύονται ως αριθμοί στο Excel, για να βρείτε τη διαφορά μεταξύ 2 τιμών χρόνου, μπορείτε εύκολα να αφαιρέσετε την ώρα έναρξης από την ώρα λήξης.
ώρα λήξης - ώρα έναρξης
Το αποτέλεσμα της αφαίρεσης θα είναι επίσης μια δεκαδική τιμή που αντιπροσωπεύει τον χρόνο που έχει παρέλθει μεταξύ των δύο χρονικών τιμών.
Παρακάτω είναι ένα παράδειγμα όπου έχω ώρα έναρξης και ώρα λήξης και έχω υπολογίσει τη διαφορά ώρας με απλή αφαίρεση.
Είναι πιθανό τα αποτελέσματά σας να εμφανίζονται σε μορφή χρόνου (και όχι σε δεκαδικές τιμές ή τιμές ώρας/λεπτού).Στο παραπάνω παράδειγμα, το αποτέλεσμα στο κελί C2 δείχνει 09:30 π.μ. αντί για 9.5.
Αυτό είναι καλό γιατί το Excel θα προσπαθήσει να αντιγράψει τη μορφοποίηση από γειτονικές στήλες.
Για να το μετατρέψετε σε δεκαδικό, αλλάξτε τη μορφή του κελιού σε "Γενικά" (αυτή η επιλογή βρίσκεται στην καρτέλα "Αρχική σελίδα" της ομάδας "Αριθμοί")
Αφού λάβετε το αποτέλεσμα, μπορείτε να το μορφοποιήσετε με διαφορετικούς τρόπους.Για παράδειγμα, μπορείτε να εμφανίσετε τιμές μόνο σε ώρες, μόνο σε λεπτά ή σε συνδυασμό ωρών, λεπτών και δευτερολέπτων.
Εδώ είναι οι διαφορετικές μορφές που μπορείτε να χρησιμοποιήσετε:
| μορφή | τι μπορεί να κάνει |
| hh | Εμφάνιση μόνο του αριθμού των ωρών που μεσολάβησαν μεταξύ δύο ημερομηνιών |
| mm | Εμφάνιση ωρών ως διψήφιο (π.χ. 04 ή 12) |
| ωω: mm | Εμφανίζει τις ώρες και τα λεπτά που έχουν περάσει μεταξύ δύο ημερομηνιών, π.χ. 10:20 |
| hh: mm: ss | Εμφανίζει ώρες, λεπτά και δευτερόλεπτα που έχουν περάσει μεταξύ δύο ημερομηνιών, π.χ. 10:20:36 |
Αν αναρωτιέστε πού και πώς να εφαρμόσετε αυτές τις προσαρμοσμένες μορφές ημερομηνίας, ακολουθήστε αυτά τα βήματα:
- Επιλέξτε τα κελιά στα οποία θα εφαρμόσετε τη μορφή ημερομηνίας
- Κρατήστε πατημένο το Control και πατήστε 1 (Command + 1 εάν χρησιμοποιείτε Mac)
- Στο παράθυρο διαλόγου Μορφοποίηση κελιών που ανοίγει, κάντε κλικ στην καρτέλα Αριθμοί (αν δεν είναι ήδη επιλεγμένο)
- Στο αριστερό παράθυρο, κάντε κλικ στην Προσαρμογή
- Εισαγάγετε οποιονδήποτε επιθυμητό κωδικό μορφής στο πεδίο Τύπος (χρησιμοποιώ ωω:λλ:δς σε αυτό το παράδειγμα)
- Κάντε κλικ στο OK
Τα παραπάνω βήματα θα αλλάξουν τη μορφή και θα σας δείξουν την τιμή σύμφωνα με τη μορφή.
Σημειώστε ότι η προσαρμοσμένη μορφοποίηση αριθμών δεν αλλάζει την τιμή στο κελί.Απλώς αλλάζει τον τρόπο εμφάνισης της τιμής.Επομένως, μπορώ να επιλέξω να εμφανίζεται μόνο η τιμή της ώρας στο κελί και εξακολουθεί να έχει την αρχική τιμή.
ΣΥΜΒΟΥΛΗ PRO: Εάν οι συνολικές ώρες υπερβαίνουν τις 24 ώρες, χρησιμοποιήστε την ακόλουθη προσαρμοσμένη μορφή αριθμού: [ωω]:λλ:δδ
Υπολογίστε τη διαφορά ώρας σε ώρες, λεπτά ή δευτερόλεπτα
Όταν αφαιρείτε τις τιμές χρόνου, το Excel επιστρέφει έναν δεκαδικό αριθμό που αντιπροσωπεύει τη διαφορά χρόνου που προκύπτει.
Δεδομένου ότι κάθε ακέραιος αριθμός αντιπροσωπεύει μια ημέρα, το κλασματικό μέρος του αριθμού θα αντιπροσωπεύει το μεγαλύτερο μέρος της ημέρας και μπορεί εύκολα να μετατραπεί σε ώρες ή λεπτά ή δευτερόλεπτα.
Υπολογίστε τη διαφορά ώρας σε ώρες
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε τον αριθμό των ωρών μεταξύ δύο τιμών χρόνου
Ο παρακάτω τύπος θα σας δώσει τη διαφορά ώρας σε ώρες:
= (B2-A2) * 24
Ο παραπάνω τύπος θα σας δώσει τον συνολικό αριθμό ωρών που μεσολάβησαν μεταξύ δύο χρονικών τιμών.
Μερικές φορές το Excel θα προσπαθήσει να σας βοηθήσει και θα σας δώσει επίσης τα αποτελέσματα σε μορφή χρόνου (εμφανίζεται παρακάτω).
Μπορείτε εύκολα να το μετατρέψετε σε μορφή αριθμού κάνοντας κλικ στην καρτέλα Αρχική σελίδα και επιλέγοντας Αριθμός ως μορφή στην ομάδα Αριθμός.
Εάν θέλετε απλώς να επιστρέψετε τον συνολικό αριθμό των ωρών που μεσολάβησαν μεταξύ δύο φορές (χωρίς κλασματικό μέρος), χρησιμοποιήστε αυτόν τον τύπο:
= Int ((B2-A2)*24)
Σημείωση: Αυτός ο τύπος λειτουργεί μόνο εάν οι δύο τιμές ώρας είναι την ίδια ημέρα.Αυτός ο τύπος θα δώσει λάθος αποτελέσματα εάν αλλάξουν οι ημερομηνίες (μία από τις τιμές ώρας είναι άλλη ημερομηνία, η δεύτερη είναι άλλη ημερομηνία).Όταν η ημερομηνία αλλάξει αργότερα σε αυτό το σεμινάριο, ρίξτε μια ματιά στην ενότητα όπου παρουσιάζω τον τύπο για τον υπολογισμό της διαφοράς ώρας.
Υπολογίστε τη διαφορά ώρας σε λεπτά
Για να υπολογίσετε τη διαφορά ώρας σε λεπτά, πρέπει να πολλαπλασιάσετε την τιμή που προκύπτει με τον συνολικό αριθμό λεπτών σε μια ημέρα (δηλαδή 1440 ή 24*60).
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε τον συνολικό αριθμό των λεπτών που μεσολάβησαν μεταξύ της ημερομηνίας έναρξης και της ημερομηνίας λήξης.
Εδώ είναι ο τύπος που μπορεί να το κάνει αυτό:
= (B2-A2)*24*60
Υπολογίστε τη διαφορά ώρας σε δευτερόλεπτα
Για να υπολογίσετε τη διαφορά ώρας σε δευτερόλεπτα, πρέπει να πολλαπλασιάσετε την τιμή που προκύπτει με τον συνολικό αριθμό δευτερολέπτων σε μια ημέρα (δηλαδή 24*60*60 ή 86400).
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως παρακάτω και θέλετε να μετρήσετε τον συνολικό αριθμό των δευτερολέπτων που έχουν περάσει μεταξύ της ημερομηνίας έναρξης και της ημερομηνίας λήξης.
Εδώ είναι ο τύπος που μπορεί να το κάνει αυτό:
=(B2-A2)*24*60*60
Υπολογίστε τη διαφορά ώρας χρησιμοποιώντας τη λειτουργία TEXT
Ένας άλλος εύκολος τρόπος για να λάβετε γρήγορα τη διαφορά ώρας χωρίς να ανησυχείτε για την αλλαγή της μορφής είναι να χρησιμοποιήσετε τη λειτουργία TEXT.
Η συνάρτηση ΚΕΙΜΕΝΟ σάς επιτρέπει να καθορίσετε μορφοποίηση σε τύπους.
=TEXT(ημερομηνία_λήξης-ημερομηνία_έναρξης, μορφή)
Η πρώτη παράμετρος είναι ο υπολογισμός που θέλετε να εκτελέσετε και η δεύτερη παράμετρος είναι η μορφή στην οποία θέλετε να εμφανίσετε τα αποτελέσματα του υπολογισμού.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε τη διαφορά ώρας μεταξύ δύο χρόνων.
Ακολουθούν μερικοί τύποι που θα σας δώσουν τα αποτελέσματα σε διαφορετικές μορφές
Εμφάνιση μόνο ωρών:
=TEXT(B2-A2,"ωω")
Ο παραπάνω τύπος θα σας δώσει μόνο το αποτέλεσμα του αριθμού των ωρών που μεσολάβησαν μεταξύ δύο χρονικών τιμών.Εάν το αποτέλεσμά σας είναι 9 ώρες 30 λεπτά, θα εξακολουθεί να δείχνει μόνο 9.
Εμφάνιση συνολικών λεπτών
=TEXT(B2-A2,"[mm]")
Εμφάνιση συνολικών δευτερολέπτων
=TEXT(B2-A2,"[ss]")
Εμφάνιση ωρών και λεπτών
=TEXT(B2-A2,"[hh]:mm")
Εμφάνιση ωρών, λεπτών και δευτερολέπτων
=TEXT(B2-A2,"ωω:λλ:δδ")
Αν αναρωτιέστε ποια είναι η διαφορά μεταξύ ωω και [ωω] στη μορφή (ή mm και [mm]), όταν χρησιμοποιείτε αγκύλες, σας δίνει τον συνολικό αριθμό ωρών μεταξύ των δύο ημερομηνιών, ακόμα και αν οι τιμές ώρας μεγαλύτερο από 24.Έτσι, εάν αφαιρείτε δύο τιμές ημερομηνίας που διαφέρουν κατά περισσότερες από 24 ώρες, η χρήση του [hh] θα σας δώσει τον συνολικό αριθμό ωρών, ενώ το hh θα σας δώσει μόνο τον αριθμό των ωρών που πέρασαν κατά την ημερομηνία λήξης.
Λάβετε τη διαφορά ώρας σε μία μονάδα (ώρες/λεπτά), αγνοώντας άλλες
Εάν θέλετε απλώς να υπολογίσετε τη διαφορά ώρας μεταξύ δύο τιμών χρόνου σε ώρες ή λεπτά ή δευτερόλεπτα, μπορείτε να χρησιμοποιήσετε τις αποκλειστικές λειτουργίες HOUR, MINUTE ή SECOND.
Κάθε μία από αυτές τις συναρτήσεις παίρνει μία παράμετρο, την τιμή χρόνου και επιστρέφει την καθορισμένη μονάδα χρόνου.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε τον συνολικό αριθμό ωρών και δευτερολέπτων που έχουν περάσει μεταξύ αυτών των δύο χρόνων.
Εδώ είναι ο τύπος για να το κάνετε αυτό:
Υπολογίστε τον αριθμό των ωρών που μεσολάβησαν μεταξύ δύο φορές
=HOUR(B2-A2)
Υπολογισμός λεπτών με βάση το αποτέλεσμα της τιμής χρόνου (εξαιρουμένων των ωρών που έχουν ολοκληρωθεί)
=MINUTE(B2-A2)
Υπολογισμός δευτερολέπτων από το αποτέλεσμα της τιμής χρόνου (εξαιρουμένων των ολοκληρωμένων ωρών και λεπτών)
=SECOND(B2-A2)
Μερικά πράγματα που πρέπει να γνωρίζετε όταν χρησιμοποιείτε αυτούς τους τύπους HOURS, MINUTE και SECOND:
- Η διαφορά μεταξύ ώρας λήξης και ώρας έναρξης δεν μπορεί να είναι αρνητική (αυτό συμβαίνει συνήθως όταν αλλάζει η ημερομηνία).Σε αυτήν την περίπτωση, αυτοί οι τύποι θα επιστρέψουν #NUM!λάθος
- Αυτοί οι τύποι χρησιμοποιούν μόνο το χρονικό τμήμα (και αγνοούν το τμήμα ημερών) της προκύπτουσας τιμής χρόνου.Έτσι, εάν η διαφορά μεταξύ ώρας λήξης και ώρας έναρξης είναι 2 ημέρες, 10 ώρες, 32 λεπτά και 44 δευτερόλεπτα, ο τύπος HOUR θα δώσει 10, ο τύπος MINUTE θα δώσει 32 και ο τύπος SECOND θα δώσει 44
Υπολογίστε το χρόνο που πέρασε τώρα (από την ώρα έναρξης)
Εάν θέλετε να υπολογίσετε τον συνολικό χρόνο που έχει παρέλθει μεταξύ της ώρας έναρξης και της τρέχουσας ώρας, μπορείτε να χρησιμοποιήσετε τον τύπο ΤΩΡΑ αντί για την ώρα λήξης.
Η συνάρτηση NOW επιστρέφει την τρέχουσα ημερομηνία και ώρα στο κελί στο οποίο χρησιμοποιείται.Είναι μια από τις συναρτήσεις που δεν δέχεται κανένα όρισμα εισαγωγής.
Έτσι, εάν θέλετε να υπολογίσετε τον συνολικό χρόνο που έχει παρέλθει μεταξύ της ώρας έναρξης και της τρέχουσας ώρας, μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
=NOW() - ώρα έναρξης
Παρακάτω είναι ένα παράδειγμα όπου έχω την ώρα έναρξης στη στήλη Α και την ώρα μέχρι στιγμής στη στήλη Β.
Εάν η διαφορά ώρας μεταξύ της ημερομηνίας και της ώρας έναρξης και της τρέχουσας ώρας είναι μεγαλύτερη από 24 ώρες, μπορείτε να μορφοποιήσετε το αποτέλεσμα ώστε να εμφανίζει το τμήμα ημερομηνίας και ώρας.
Μπορείτε να το κάνετε αυτό με τον ακόλουθο τύπο TEXT:
=TEXT(NOW()-A2,"ηη ωω:δδ:λλ")
Μπορείτε επίσης να επιτύχετε το ίδιο πράγμα αλλάζοντας την προσαρμοσμένη μορφή του κελιού (όπως περιγράφηκε νωρίτερα σε αυτό το σεμινάριο) έτσι ώστε να εμφανίζει το τμήμα ημερομηνίας και ώρας.
Εάν η ώρα έναρξης έχει μόνο το μέρος του χρόνου, το Excel το αντιμετωπίζει ως ώρα της 1990ης Ιανουαρίου 1.
Σε αυτήν την περίπτωση, εάν χρησιμοποιήσετε τη συνάρτηση NOW για να υπολογίσετε τον χρόνο που έχει παρέλθει μέχρι τώρα, θα σας δώσει λάθος αποτέλεσμα (γιατί η τιμή που προκύπτει θα περιέχει επίσης τον συνολικό αριθμό των ημερών που έχουν περάσει από την 1990η Ιανουαρίου 1).
Σε αυτή την περίπτωση μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
=NOW()-INT(NOW())-A2
Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση INT για να αφαιρέσει το τμήμα ημερών από την τιμή που επιστρέφεται από τη συνάρτηση τώρα, η οποία στη συνέχεια χρησιμοποιείται για τον υπολογισμό της διαφοράς ώρας.
Σημειώστε ότι το NOW είναι μια πτητική συνάρτηση που ενημερώνεται όταν αλλάζει το φύλλο, αλλά όχι σε πραγματικό χρόνο
Υπολογισμός της ώρας κατά την αλλαγή της ημερομηνίας (υπολογισμός και εμφάνιση αρνητικής ώρας στο Excel)
Οι μέθοδοι που περιγράφηκαν μέχρι τώρα λειτουργούν καλά εάν η ώρα λήξης είναι μεταγενέστερη από την ώρα έναρξης.
Ωστόσο, το πρόβλημα παρουσιάζεται όταν η ώρα λήξης είναι χαμηλότερη από την ώρα έναρξης.Αυτό συμβαίνει συνήθως όταν συμπληρώνετε ένα φύλλο ώρας και εισάγετε μόνο την ώρα και όχι ολόκληρη την ημερομηνία και ώρα.
Σε αυτήν την περίπτωση, εάν εργάζεστε σε νυχτερινή βάρδια και η ημερομηνία αλλάξει, η ώρα λήξης μπορεί να είναι νωρίτερα από την ώρα έναρξης.
Για παράδειγμα, εάν ξεκινήσετε τη δουλειά στις 6:00 μ.μ. και ολοκληρώσετε την εργασία και κάνετε παύση στις 9:00 π.μ.
Εάν χρησιμοποιούσατε μόνο τιμές χρόνου, τότε η αφαίρεση της ώρας έναρξης από την ώρα λήξης θα έδινε μια αρνητική τιμή 9 ωρών (9 – 18).
Το Excel δεν μπορεί να χειριστεί τις αρνητικές τιμές χρόνου (και, ως εκ τούτου, τους ανθρώπους, εκτός εάν μπορείτε να ταξιδέψετε στο χρόνο)
Σε αυτήν την περίπτωση, χρειάζεστε έναν τρόπο για να προσδιορίσετε ότι η ημερομηνία έχει αλλάξει και θα πρέπει να υπολογίσετε ανάλογα.
Ευτυχώς, υπάρχει μια πολύ απλή λύση.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως παρακάτω, όπου έχω ώρα έναρξης και ώρα λήξης.
Όπως θα παρατηρήσετε, μερικές φορές η ώρα έναρξης είναι το βράδυ και η ώρα λήξης είναι το πρωί (που υποδηλώνει ότι ήταν μια νυχτερινή βάρδια και η ημέρα είχε αλλάξει).
Εάν χρησιμοποιήσω τον παρακάτω τύπο για να υπολογίσω τη διαφορά ώρας, εμφανίζει ένα σύμβολο λίβρας (επισημασμένο με κίτρινο χρώμα στην παρακάτω εικόνα) στα κελιά όπου το αποτέλεσμα είναι αρνητική τιμή.
= Β2-Α2
Αυτός είναι ένας τύπος IF, εάν η τιμή διαφοράς χρόνου είναι αρνητική, εάν είναι αρνητική, επιστρέφει το σωστό αποτέλεσμα
=IF((B2-A2)<0,1-(A2-B2),(B2-A2))
Αν και αυτό λειτουργεί καλά στις περισσότερες περιπτώσεις, εξακολουθεί να μην είναι αρκετό εάν οι ώρες έναρξης και λήξης έχουν διαφορά μεγαλύτερη από 24 ώρες.Για παράδειγμα, κάποιος συνδέεται στις 1:9 π.μ. της 00ης ημέρας και αποσυνδέεται στις 2:11 π.μ. της 00ης ημέρας.
Δεδομένου ότι αυτό είναι περισσότερο από 24 ώρες, δεν υπάρχει τρόπος να γνωρίζουμε εάν το άτομο το κόβει μετά από 2 ώρες ή 26 ώρες.
Αν και ο καλύτερος τρόπος για να το διορθώσετε είναι να βεβαιωθείτε ότι η καταχώριση περιλαμβάνει την ημερομηνία και την ώρα, εάν είναι μόνο η ώρα με την οποία εργάζεστε, τότε ο παραπάνω τύπος θα κάνει το μεγαλύτερο μέρος του προβλήματος (λαμβάνοντας υπόψη ότι είναι απίθανο κάποιος να εργάζεται περισσότερο από 24 ώρες)
Προσθήκη και αφαίρεση χρόνου στο Excel
Μέχρι στιγμής έχουμε δει παραδείγματα ωρών έναρξης και λήξης, πρέπει να βρούμε τη διαφορά ώρας.
Το Excel σάς επιτρέπει επίσης να προσθέτετε ή να αφαιρείτε εύκολα σταθερές τιμές χρόνου από τις υπάρχουσες τιμές ημερομηνίας και ώρας.
Για παράδειγμα, ας υποθέσουμε ότι έχετε μια λίστα εργασιών στην ουρά, όπου κάθε εργασία διαρκεί έναν καθορισμένο χρόνο και θέλετε να μάθετε πότε τελειώνει κάθε εργασία.
Σε αυτήν την περίπτωση, μπορείτε εύκολα να προσθέσετε τον χρόνο που χρειάζεται για κάθε εργασία στην ώρα έναρξης για να δείτε πότε αναμένεται να ολοκληρωθεί η εργασία.
Εφόσον το Excel αποθηκεύει τις τιμές ημερομηνίας και ώρας ως αριθμούς, πρέπει να βεβαιωθείτε ότι η ώρα που προσπαθείτε να προσθέσετε είναι σε μορφή που ακολουθεί ήδη το Excel.
Για παράδειγμα, εάν προσθέσετε 1 σε μια ημερομηνία στο Excel, θα σας δώσει την επόμενη ημερομηνία.Αυτό συμβαίνει επειδή το 1 αντιπροσωπεύει μια ολόκληρη ημέρα (που ισοδυναμεί με 24 ώρες) στο Excel.
Επομένως, εάν θέλετε να προσθέσετε 1 ώρα σε μια υπάρχουσα τιμή χρόνου, δεν μπορείτε να προχωρήσετε και απλώς να προσθέσετε 1 σε αυτήν.Πρέπει να φροντίσετε να μετατρέψετε την τιμή της ώρας σε ένα κλασματικό μέρος που αντιπροσωπεύει μια ώρα.Το ίδιο ισχύει και για την προσθήκη λεπτών και δευτερολέπτων.
Χρησιμοποιήστε τη λειτουργία χρόνου
Οι συναρτήσεις χρόνου στο Excel λαμβάνουν τιμές ώρας, λεπτών και δευτερολέπτων και τις μετατρέπουν σε δεκαδικό αριθμό που αντιπροσωπεύει την ώρα.
Για παράδειγμα, αν ήθελα να προσθέσω 4 ώρες στον υπάρχοντα χρόνο, θα μπορούσα να χρησιμοποιήσω τον ακόλουθο τύπο:
= ώρα έναρξης + TIME (4,0,0)
Αυτό είναι χρήσιμο εάν γνωρίζετε τις ώρες, τα λεπτά και τα δευτερόλεπτα που πρέπει να προσθέσετε σε μια υπάρχουσα ώρα και μπορείτε απλώς να χρησιμοποιήσετε τη λειτουργία TIME χωρίς να ανησυχείτε για τη σωστή μετατροπή της ώρας σε δεκαδική τιμή.
Επίσης, σημειώστε ότι η συνάρτηση TIME λαμβάνει υπόψη μόνο το ακέραιο τμήμα των τιμών ωρών, λεπτών και δευτερολέπτων που εισάγετε.Για παράδειγμα, αν χρησιμοποιήσω 5.5 ώρες στη συνάρτηση TIME, προσθέτει απλώς 5 ώρες και αγνοεί το κλασματικό μέρος.
Σημειώστε επίσης ότι η συνάρτηση TIME μπορεί να προσθέσει μόνο τιμές που είναι λιγότερες από 24 ώρες.Αυτό θα σας δώσει ένα λανθασμένο αποτέλεσμα εάν η τιμή ώρας υπερβαίνει τις 24.
Το ίδιο ισχύει για τα μέρη λεπτών και δευτερολέπτων, η συνάρτηση θα λαμβάνει υπόψη μόνο τιμές μικρότερες από 60 λεπτά και 60 δευτερόλεπτα
Ακριβώς όπως χρησιμοποίησα τη συνάρτηση TIME για να προσθέσω χρόνο, μπορείτε επίσης να αφαιρέσετε χρόνο.Απλώς αλλάξτε το σύμβολο + στον παραπάνω τύπο σε ένα σύμβολο μείον
Χρησιμοποιήστε βασική αριθμητική
Ενώ η λειτουργία χρόνου είναι εύκολη στη χρήση, έχει ορισμένους περιορισμούς (όπως αναφέρθηκε παραπάνω).
Εάν θέλετε περισσότερο έλεγχο, μπορείτε να χρησιμοποιήσετε την αριθμητική μέθοδο που θα καλύψω εδώ.
Η ιδέα είναι απλή - μετατρέψτε μια τιμή χρόνου σε μια δεκαδική τιμή που αντιπροσωπεύει το μέρος της ημέρας, την οποία μπορείτε στη συνέχεια να προσθέσετε σε οποιαδήποτε τιμή χρόνου στο Excel.
Για παράδειγμα, εάν θέλετε να προσθέσετε 24 ώρες σε μια υπάρχουσα τιμή χρόνου, θα μπορούσατε να χρησιμοποιήσετε τον ακόλουθο τύπο:
= ώρα έναρξης + 24/24
Σημαίνει απλώς ότι προσθέτω μια ημέρα στην υπάρχουσα τιμή χρόνου.
Λαμβάνοντας τώρα την ίδια ιδέα, ας υποθέσουμε ότι θέλετε να προσθέσετε 30 ώρες στην τιμή χρόνου, μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
= ώρα έναρξης + 30/24
Ο παραπάνω τύπος κάνει το ίδιο πράγμα, όπου το ακέραιο μέρος του (30/24) αντιπροσωπεύει τον συνολικό αριθμό ημερών του χρόνου που θέλετε να προσθέσετε και το κλασματικό μέρος αντιπροσωπεύει ώρες/λεπτά/δευτερόλεπτα
Ομοίως, εάν θέλετε να προσθέσετε έναν συγκεκριμένο αριθμό λεπτών στην τιμή του χρόνου, μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
=Ωρα έναρξης +(Προσθήκη λεπτών)/24*60
Εάν έχετε δευτερόλεπτα να προσθέσετε, μπορείτε να χρησιμοποιήσετε αυτόν τον τύπο:
= ώρα έναρξης + (πρόσθετα λεπτά)/24*60*60
Αν και αυτή η μέθοδος δεν είναι τόσο απλή όσο η χρήση της συνάρτησης χρόνου, τη βρίσκω πολύ καλύτερη γιατί λειτουργεί σε όλες τις περιπτώσεις και ακολουθεί τις ίδιες έννοιες.Σε αντίθεση με τη λειτουργία ώρας, δεν χρειάζεται να ανησυχείτε μήπως προσθέσετε χρόνο μικρότερο ή μεγαλύτερο από 24 ώρες
Μπορείτε επίσης να ακολουθήσετε την ίδια έννοια αφαιρώντας τον χρόνο.Απλώς αλλάξτε το + σε αρνητικό πρόσημο στον παραπάνω τύπο
Πώς να αθροίσετε το χρόνο στο Excel
Μερικές φορές μπορεί να θέλετε να προσθέσετε γρήγορα τιμές all time στο Excel.Η προσθήκη πολλαπλών τιμών χρόνου στο Excel είναι εύκολη (μόνο ένας απλός τύπος SUM)
Ωστόσο, όταν προσθέτετε χρόνο στο Excel, υπάρχουν μερικά πράγματα που πρέπει να γνωρίζετε, ειδικά τη μορφή κελιού που θα σας δείξει τα αποτελέσματα.
Ας δούμε ένα παράδειγμα.
Παρακάτω έχω μια λίστα εργασιών στη στήλη Β και τον χρόνο που χρειάζεται για κάθε εργασία, και θέλω να προσθέσω γρήγορα αυτούς τους χρόνους και να γνωρίζω τον συνολικό χρόνο που θα χρειαστούν όλες αυτές οι εργασίες.
Στο κελί B9 χρησιμοποίησα έναν απλό τύπο SUM για να υπολογίσω τον συνολικό χρόνο που θα χρειαστούν όλες αυτές οι εργασίες και μου δίνει μια τιμή 18:30 (που σημαίνει ότι χρειάζονται 18 ώρες και 20 λεπτά για να ολοκληρωθούν όλες αυτές οι εργασίες)
Μέχρι εδώ καλά!
Πώς να αθροίσετε πάνω από 24 ώρες στο Excel
Δείτε τώρα τι συμβαίνει όταν αλλάξω τον χρόνο ολοκλήρωσης της εργασίας 2 από 1 ώρα σε 10 ώρες.
Το αποτέλεσμα δείχνει τώρα 03:20, που σημαίνει ότι θα χρειαστούν 3 ώρες και 20 λεπτά για να ολοκληρωθούν όλες αυτές οι εργασίες.
Αυτό είναι λάθος (προφανώς)
Το πρόβλημα εδώ δεν είναι ότι το Excel έχει χαλάσει.Το πρόβλημα εδώ είναι ότι τα κελιά είναι μορφοποιημένα ώστε να εμφανίζουν μόνο το χρονικό τμήμα της τιμής που προκύπτει.
Και επειδή η τιμή που προκύπτει εδώ υπερβαίνει τις 24 ώρες, το Excel αποφασίζει να μετατρέψει το τμήμα 24 ωρών σε ημέρα, να το αφαιρέσει από την τιμή που εμφανίζεται στον χρήστη και να εμφανίσει μόνο τις υπόλοιπες ώρες, λεπτά και δευτερόλεπτα.
Ευτυχώς, αυτό είναι εύκολο να διορθωθεί.
Το μόνο που χρειάζεται να κάνετε είναι να αλλάξετε τη μορφή του κελιού για να το αναγκάσετε να εμφανίζει τις ώρες ακόμα κι αν είναι περισσότερες από 24 ώρες.
Ακολουθούν ορισμένες μορφές που μπορείτε να χρησιμοποιήσετε:
| μορφή | Αναμενόμενα αποτελέσματα |
| [h]: mm | 28:30 |
| [m]: ss | 1710:00 |
| d “D” ωω:mm | 04η ημέρα 30:XNUMX |
| d "D" hh "Min" ss "Sec" | 1 ημέρα 04 λεπτά 00 δευτερόλεπτα |
| d "Day" hh "Minute" ss "Seconds" | 1 ημέρα 04 λεπτά 00 δευτερόλεπτα |
Μπορείτε να αλλάξετε τη μορφή μεταβαίνοντας στο παράθυρο διαλόγου Μορφοποίηση κελιών και εφαρμόζοντας μια προσαρμοσμένη μορφή ή να χρησιμοποιήσετε τη συνάρτηση TEXT και να χρησιμοποιήσετε οποιαδήποτε από τις παραπάνω μορφές στον ίδιο τον τύπο
Μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο TEXT για να εμφανίσετε την ώρα ακόμα κι αν υπερβαίνει τις 24 ώρες:
=TEXT(SUM(B2:B7),"[h]:mm:δδ")
Ή αν θέλετε να μετατρέψετε ώρες 24 ωρών σε ημέρες, χρησιμοποιήστε τον ακόλουθο τύπο:
=TEXT(SUM(B2:B7),"ηη ωω:λλ:δδ")
Τα αποτελέσματα εμφανίζουν κατακερματισμό (###) αντί για ημερομηνία/ώρα (αιτία + επιδιόρθωση)
Σε ορισμένες περιπτώσεις, μπορεί να διαπιστώσετε ότι αντί να εμφανίζει τιμές χρόνου, το Excel εμφανίζει σημάδια λίβρας σε κελιά.
Ακολουθούν ορισμένες πιθανές αιτίες και πώς να τις διορθώσετε:
Οι στήλες δεν είναι αρκετά φαρδιές
Όταν ένα κελί δεν έχει αρκετό χώρο για να εμφανίσει την πλήρη ημερομηνία, μπορεί να εμφανίσει ένα σύμβολο λίρας.
Έχει μια εύκολη επιδιόρθωση - αλλάξτε το πλάτος της στήλης και κάντε το ευρύτερο.
αρνητική τιμή ημερομηνίας
Οι τιμές ημερομηνίας ή ώρας στο Excel δεν μπορούν να είναι αρνητικές.Εάν υπολογίζετε τη διαφορά ώρας και το αποτέλεσμα είναι αρνητικό, το Excel θα εμφανίσει το σύμβολο κατακερματισμού.
Διορθώστε τη μέθοδο αλλαγής του τύπου για να δώσετε το σωστό αποτέλεσμα.Για παράδειγμα, εάν υπολογίζετε τη διαφορά ώρας μεταξύ δύο χρόνων και αλλάζει η ημερομηνία, θα πρέπει να προσαρμόσετε τον τύπο για να το λάβετε υπόψη.
Σε άλλες περιπτώσεις, μπορείτε να χρησιμοποιήσετε τη λειτουργία ABS για να μετατρέψετε τις αρνητικές τιμές χρόνου σε θετικούς αριθμούς για σωστή εμφάνιση.Εναλλακτικά, μπορείτε να χρησιμοποιήσετε έναν τύπο IF για να ελέγξετε εάν το αποτέλεσμα είναι αρνητικό και να επιστρέψετε μια πιο σημαντική τιμή.
Σε αυτό το σεμινάριο, κάλυψα θέματα σχετικά με τον υπολογισμό του χρόνου στο Excel (όπου μπορείτε να υπολογίσετε τις διαφορές ώρας, να προσθέσετε και να αφαιρέσετε χρόνο, να εμφανίσετε χρόνο σε διαφορετικές μορφές και να αθροίσετε τιμές χρόνου)
Ελπίζω να σας φάνηκε χρήσιμο αυτό το σεμινάριο.
Συχνές ερωτήσεις σχετικά με τον υπολογισμό της πρόσθεσης και της αφαίρεσης χρόνου στο Excel
Στο Excel, μπορείτε να χρησιμοποιήσετε τη συνάρτηση ώρας για να προσθέσετε χρόνο.Λαμβάνοντας για παράδειγμα τα λεπτά, εάν υπάρχει ώρα '2:2' στο κελί B30 και, στη συνέχεια, εισαγάγετε '=B2+time(2)' στο C0,30,0, το αποτέλεσμα θα είναι '2:3' που εμφανίζεται στο Γ00.
Για να μειώσετε το χρόνο στο Excel, μπορείτε να χρησιμοποιήσετε τη συνάρτηση χρόνου.Για παράδειγμα, εάν έχετε χρόνο '3:30' στο B2 και, στη συνέχεια, πληκτρολογήσετε '=B2-time(2)' στο C0,30,0, το αποτέλεσμα θα είναι '2:3' στο C00.
Για να υπολογίσετε ένα χρονικό διάστημα στο Excel, απλώς αφαιρέστε την ώρα έναρξης από την ώρα λήξης.Για παράδειγμα, εάν υπάρχει ώρα έναρξης '2:1' στο B30 και ώρα λήξης '2:3' στο C00, εισαγάγετε '=C2-B2' στο D2.
Το προεπιλεγμένο σύστημα ημερομηνίας και ώρας του Excel βασίζεται στο 24ωρο ρολόι. Εάν το αποτέλεσμα υπολογισμού ώρας υπερβαίνει τις 24 ώρες, το Excel θα το μετατρέψει σε μορφή ημερομηνίας.Για να λύσετε αυτό το πρόβλημα, μπορείτε να ορίσετε τη μορφή των κελιών που περιλαμβάνονται στον υπολογισμό σε "[h]:mm".
Βεβαιωθείτε ότι μετά την προσθήκη ή την αφαίρεση χρόνων, η αριθμητική μορφή του πεδίου αποτελεσμάτων παραμένει σε μορφή χρόνου.Συνήθως, μπορείτε να επιλέξετε το κελί, στη συνέχεια να επιλέξετε "Μορφή αριθμού" από τη γραμμή εργαλείων και, στη συνέχεια, να επιλέξετε "Ώρα" για να το ορίσετε στη μορφή ώρας.
Το παρακάτω είναι ένα δείγμα φόρμας:
| μια φορά | ώρα δύο | χρόνος διαφοράς |
|---|---|---|
| 2:30 | 3:00 | 0:30 |
| 10:00 | 8:00 | 2:00 |
