Hoe berekent u het aantal uren tussen tijdstippen die na middernacht vallen in Excel?
Stel dat u een werkschema beheert in Excel en u moet werktijden van medewerkers bijhouden die laat op een dag beginnen en na middernacht eindigen, dus doorgaan naar de volgende dag. Kolom A registreert bijvoorbeeld de begintijden op de huidige dag en kolom B de eindtijden na middernacht op de volgende dag. Wanneer u probeert het tijdsverschil te berekenen met een eenvoudige aftrekformule zoals „=B2-A2”, toont Excel een onjuist of negatief resultaat, zoals in de onderstaande afbeelding. Dit komt doordat Excel-tijdberekeningen om middernacht opnieuw beginnen, waardoor direct aftrekken foutieve waarden oplevert voor perioden die middernacht overschrijden. Hoe kunt u de totale gewerkte uren tussen twee tijdstippen nauwkeurig berekenen wanneer de eindtijd na middernacht ligt in Excel?
Bereken de uren tussen twee tijdstippen na middernacht met een formule
Bereken uren tussen tijdstippen na middernacht met VBA-code (geavanceerde automatisering)
Bereken de uren tussen twee tijdstippen na middernacht met een formule
Voor een nauwkeurige berekening van het aantal uren tussen twee tijdstippen — waarbij de eindtijd na middernacht valt — kunt u een eenvoudige formule toepassen die rekening houdt met tijdsverschillen over twee dagen. Deze aanpak is ideaal voor kleine tot matig grote datasets of wanneer uw logica alleen tijdsverschillen hoeft te verwerken die middernacht overschrijden.
Hoe het werkt: De formule „=(B2-A2+(B2<A2))*24” controleert of de eindtijd (B2) vroeger is dan de begintijd (A2). Zo ja, dan wordt er 1 dag bij opgeteld (aangezien Excel tijd opslaat als fracties van een 24-uursdag), zodat u altijd een positieve urenberekening krijgt.
1. Voer de volgende formule in een lege cel naast uw tijdgegevens in (bijvoorbeeld in cel C2):
=(B2-A2+(B2<A2))*24Uitleg: Hierin bevat A2 de begintijd en B2 de eindtijd (na middernacht). Pas de celverwijzingen aan op basis van uw gegevensindeling.
2. Nadat u de formule hebt ingevoerd en op Enter hebt gedrukt, gebruikt u de vulgreep om de formule naar beneden te slepen naar andere rijen waar dit van toepassing is. Excel berekent dan direct de tijdsverschillen, rekening houdend met de overgang na middernacht.
Tips:
- Zorg ervoor dat alle tijdcellen zijn opgemaakt als Tijd (klik met de rechtermuisknop > Celopmaak instellen > Tijd).
- Deze formule geeft uren weer als decimaal getal. Voor een resultaat in uren en minuten kunt u de
TEKST-opmaak gebruiken, bijvoorbeeld=TEXT(B2-A2+(B2<A2),"h:mm"). De oorspronkelijke formule is echter ideaal wanneer u decimale uren nodig heeft voor salarisberekeningen of rapportages. - Als uw gegevens ongeldige tijdsaanduidingen bevatten, controleer dan op typefouten of waarden die Excel niet herkent als tijd.
Veelvoorkomende fouten:
- Als u een #WAARDE!-fout krijgt, controleer dan of beide kolommen geldige tijden bevatten en geen tekst.
- Negatieve of onverwachte resultaten duiden er meestal op dat de celverwijzingen in de formule niet kloppen of dat een tijd onjuist is ingevoerd.
Samenvatting: Deze oplossing is ideaal voor standaard ploegschema’s of eenvoudige nachtelijke tijdsregistratie, mits uw gegevens passen in een eenvoudige tweekolomsopzet en u geen complexere bedrijfslogica nodig hebt.
Bereken uren tussen tijdstippen na middernacht met VBA-code (geavanceerde automatisering)
Als u een grote hoeveelheid tijdregistratiegegevens moet verwerken of de urenberekening wilt automatiseren voor complexe scenario’s—zoals aangepaste afronding, uitsluitingen of variabele pauzes—bespaart een VBA-macro u tijd en vermindert het handmatige fouten. Deze methode is ideaal voor gevorderde gebruikers of wie te maken heeft met wisselende ploegenindelingen, ongebruikelijke afsnijpunten of batchverwerking.
Toepasselijke scenario’s:Kies deze oplossing als u:
- Verwerk regelmatig honderden of duizenden rijen
- Hebt u logica nodig die verder reikt dan een eenvoudige overgang na middernacht, zoals variabele overurenregels of het aftrekken van pauzes?
- Gebruikt u liever een oplossing die automatisch opnieuw wordt berekend met minimale handmatige invoer
Nadelen: Gebruikers moeten op hun gemak zijn met het inschakelen van macro’s, en ontvangers van de werkmap moeten mogelijk ook VBA toestaan of hun macrobeveiligingsinstellingen aanpassen.
1. Open het Excel-werkboek en druk op Alt + F11 om de editor voor Visual Basic for Applications (VBA) te openen. Klik in de VBA-omgeving op Invoegen > Module om een nieuwe module te maken en plak de onderstaande code in het codovenster:
Sub CalculateHoursAcrossMidnight()
Dim rng As Range
Dim cell As Range
Dim startTime As Date
Dim endTime As Date
Dim resultCol As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range containing start and end times (two columns)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resultCol = rng.Columns(rng.Columns.Count).Column + 1
For Each cell In rng.Columns(1).Cells
startTime = cell.Value
endTime = cell.Offset(0, 1).Value
If IsDate(startTime) And IsDate(endTime) Then
If endTime < startTime Then
cell.Offset(0, 2).Value = (endTime - startTime + 1) * 24
Else
cell.Offset(0, 2).Value = (endTime - startTime) * 24
End If
Else
cell.Offset(0, 2).Value = "Invalid Time"
End If
Next cell
MsgBox "Calculation complete! Hours between times after midnight are now in the next column.", vbInformation, xTitleId
End Sub2. Sluit de VBA-editor en keer terug naar Excel om de macro uit te voeren. Selecteer het bereik in uw werkblad dat zowel de kolommen met begintijden als eindtijden bevat. Druk vervolgens op de F5-toets of klik op de Uitvoeren-knop.
De macro vraagt u om een bereik te selecteren dat zowel de begintijden als de eindtijden bevat (naast elkaar). De resultaten — het aantal uren tussen de tijdstippen, aangepast voor de overgang na middernacht — worden weergegeven in de kolom direct rechts van uw gegevensbereik.
Tips en probleemoplossing:
- Zowel de kolom met begintijden als die met eindtijden moet geldige datum-/tijdwaarden bevatten; cellen met niet-herkende indelingen worden gemarkeerd als ‘Ongeldige tijd’.
- Om de macro opnieuw te gebruiken of aan te passen voor andere werkbladen, wijzigt u de selectie of past u de kolomverschuivingen naar wens aan.
- Als de macro niet zichtbaar of uitvoerbaar is, controleer dan of macro’s zijn ingeschakeld in uw beveiligingsinstellingen via Excel (Bestand > Opties > Trustcentrum > Macro-instellingen).
- Deze aanpak is eenvoudig aan te passen aan uw eigen bedrijfsregels: voeg indien nodig extra VBA-logica toe voor rustpauzes, afronding van tijden of de berekening van overuren.
Samenvatting: De geautomatiseerde VBA-methode is vooral nuttig voor gebruikers die grote roosters beheren, herhaalde berekeningen moeten uitvoeren of organisatiespecifieke logica willen toepassen bij tijdregistratie. Eenmaal ingesteld elimineert deze methode repetitief handmatig werk en vermindert het risico op fouten bij de berekening van nachtdiensten.
Ontgrendel de magie van Excel met KUTOOLS AI
- Slimme uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak grafieken — allemaal met eenvoudige opdrachten.
- aangepaste formules: Genereer op maat gemaakte formules om uw workflows te stroomlijnen.
- VBA-programmeren: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules moeiteloos.
- Tekstvertaling: Doorbreek taalbarrières in uw spreadsheets.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins