Hoe negatieve tijd correct weergeven in Excel?
Werken met tijdberekeningen in Excel kan uitdagend zijn wanneer je een latere tijd van een vroegere tijd aftrekt. Als je bijvoorbeeld 12:20 van 10:15 aftrekt, retourneert Excel een ###### fout in plaats van een negatieve tijdwaarde, zoals te zien is in de onderstaande schermafbeeldingen. Dit probleem komt vaak voor omdat Excel standaard alleen positieve tijdwaarden ondersteunt in zijn standaard datum systeem. Het oplossen van dit probleem is belangrijk voor nauwkeurige planning, urenregistratie en situaties waarin je negatieve tijdsduur of overuren moet weergeven.
➤ Negatieve tijd correct weergeven door het standaard datum systeem van Excel te wijzigen
➤ Negatieve tijd correct weergeven met formules
➤ Negatieve tijd weergeven met VBA-code (aangepast tekstformaat)
Negatieve tijd correct weergeven door het standaard datum systeem van Excel te wijzigen
Bij het werken met tijdberekeningen is een eenvoudige manier om negatieve tijdwaarden weer te geven het overschakelen van het werkboek naar het 1904-datum systeem. Dit laat negatieve resultaten correct zien (bijv. “-2:05”) in plaats van fouten. Handig als je vaak negatieve tijdsduren moet tonen, zoals bij projectvolgsystemen of dienstroosters.
Opmerkingen:
- Het overschakelen naar het 1904-datum systeem verschuift alle bestaande datums in het werkboek met ongeveer 1.462 dagen (~4 jaar). Controleer andere datumvelden na de wijziging.
- Deze instelling is werkboek-specifiek (het heeft geen invloed op andere bestanden).
- Voor samenwerking: houd er rekening mee dat gebruikers die het standaard 1900-datum systeem gebruiken verschillende resultaten kunnen zien als werkboeken worden gemixt.
- Je kunt niet tegelijkertijd zowel het 1900- als het 1904-datum systeem in hetzelfde werkboek gebruiken.
1. Open het dialoogvenster Excel Opties. In Excel 2010/2013 ga je naar Bestand > Opties. In Excel 2007 klik je op de Office-knop en kies je Excel Opties.
2. Klik in Excel Opties op Geavanceerd. Onder Bij het berekenen van dit werkboek, vink aan Gebruik 1904-datum systeem. Zie onderstaande schermafbeelding:
3. Klik op OK. Negatieve tijdsverschillen worden nu correct weergegeven, zoals hier te zien is:
Tips:
- Als het behoud van historische datumnauwkeurigheid cruciaal is, overweeg dan alternatieve methoden (bijv. aangepaste tekstuitvoer via formule of VBA) in plaats van het datum systeem te wijzigen.
- Documenteer de wijziging van het datum systeem in het werkboek (bijv. op een “LeesMij” blad) om verwarring voor andere gebruikers te voorkomen.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Negatieve tijd correct weergeven met formules
Als je workflow niet toestaat om het datum systeem te veranderen (omwille van compatibiliteit of rapportage), kun je correcte negatieve tijdweergave bereiken met behulp van formules. Deze methode toont negatieve duur als tekst, wat duidelijke visuele resultaten oplevert zonder Excel-fouten te veroorzaken. Deze aanpak wordt veel gebruikt in scenario's zoals tekortberekening van werkuur, projectoverschrijdingstracking of vergelijking van verstreken tijd.
1. Voer de tijden die je wilt berekenen in twee aparte cellen in (bijv., A1 en A2). Typ in een lege cel de volgende formule om het negatieve verschil te berekenen en op te maken:
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Hier vertegenwoordigen A1 en A2 de twee tijdwaarden. Pas de celverwijzingen aan indien nodig voor je data. Deze formule geeft een resultaat als tekst, zodat je negatieve tekens kunt zien, zelfs als Excel normaal gesproken een fout zou retourneren bij de berekening.
2. Druk op Enter. Het resultaat zal verschijnen als een opgemaakte tijdstring met een minteken als deze negatief is, zoals hieronder te zien is:
Alternatieve formule:
Je kunt ook deze formule gebruiken, die conditioneel een minteken weergeeft wanneer het resultaat negatief is:
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)In deze configuratie is A2 de kleinere of vroegere tijd, en A1 de latere tijd, maar je moet deze aanpassen zoals geschikt is voor je berekeningsrichting. Deze methode produceert een tekstresultaat voor negatieve waarden, wat handig is voor rapportage, maar houd er rekening mee dat de resulterende waarden niet als tijd kunnen worden getotaaliseerd zonder extra conversie.
Opmerkingen:
- Resultaten zijn tekst, geen echte tijdswaarden, dus verdere tijdberekeningen werken niet tenzij je de uitkomst terug converteert naar een tijdformaat.
- De formule-aanpak is onafhankelijk van het werkboek, nuttig voor delen en voorkomt globale instellingsconflicten.
- Let op dat je de juiste celrichtingen refereert om logische fouten te voorkomen (bijv., aftrekken van einde tot start om een negatief resultaat te krijgen).
Negatieve tijd weergeven met VBA-code (aangepast tekstformaat)
Een andere effectieve methode om negatieve tijd in Excel af te handelen is door gebruik te maken van een VBA-macro. Dit is vooral handig wanneer je een naadloze manier wilt om automatisch negatieve tijdscalcules in je werkblad te detecteren en weer te geven – zelfs voor grote batches – zonder systeeminstellingen te wijzigen of formules voor elke cel te gebruiken. Deze methode is geschikt voor gevorderde gebruikers die continue tijdinvoer beheren, zoals medewerkersploegenlogs, en voorkomt de ###### fout door negatieve tijdresultaten om te zetten in aangepaste tekststrings (bijv., voorafgegaan door een minteken).
Voordelen: Maakt batchverwerking van geselecteerde cellen mogelijk, behoudt originele gegevens, kan resultaten indien nodig formatteren en beïnvloedt de datum systeeminstellingen van Excel niet.
Nadelen: Vereist het inschakelen van macro's, niet geschikt als je Excel's native tijdswaarden voor berekeningen wilt behouden naast de weergave.
Zorg en tips:
- Sla altijd een back-up van je bestand op voordat je VBA-scripts uitvoert.
- Deze oplossing transformeert negatieve tijdresultaten in tekst. Als verdere berekeningen nodig zijn, gebruik dan de originele numerieke gegevens, niet het macro-genererde weergaveresultaat.
- Macro's kunnen worden uitgeschakeld door je IT-beleid. Schakel macro's in Excel in om deze oplossing te gebruiken.
1. Klik in Excel op Ontwikkelaar > Visual Basic. Klik in het VBA-editor venster op Invoegen > Module, plak vervolgens de volgende code in het modulevenster:
Sub DisplayNegativeTimeAsText()
Dim WorkRng As Range
Dim Cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select time calculation result cells", xTitleId, WorkRng.Address, Type:=8)
For Each Cell In WorkRng
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
If InStr(Cell.NumberFormat, ":") > 0 Then
If Cell.Value < 0 Then
Cell.NumberFormat = "@"
Cell.Value = "-" & Format(Abs(Cell.Value), "h:mm")
End If
End If
End If
Next
End Sub2. Om de macro uit te voeren, klik op de 
Uitvoeren knop in de VBA-editor of druk op F5. Er verschijnt een dialoogvenster waarmee je de celbereik kunt selecteren dat je tijdverschilresultaten bevat (zelfs die met de ###### fout).
De macro scant automatisch het geselecteerde bereik. Voor elke cel die een negatieve tijdwaarde bevat (die normaal ###### zou laten zien), verandert het de cel in tekstformaat en converteert de waarde naar een leesbaar formaat, zoals "-2:05". Positieve tijdresultaten blijven ongewijzigd. Als je terug wilt naar het normale tijdformaat, moet je de oorspronkelijke celwaarden herstellen of een ongedaan maken/herstellen actie gebruiken.
Problemen oplossen en verdere tips:
- Als je een bereik selecteert zonder tijdverschillen (bijv., lege cellen of niet-tijdwaarden), slaat de macro deze veilig over.
- Als er geen cellen zijn geselecteerd, vraagt de macro om een bereik en beïnvloedt deze andere gegevens niet.
- Controleer altijd de opmaak na uitvoering; de beïnvloede cellen zijn nu tekst, dus originele tijdscalcules werken niet meer tot ze zijn teruggezet.
- Om de macro uit te breiden of aan te passen voor andere weergaveformaten of langere uur-minuut-seconde structuren, pas de
Format(Abs(Cell.Value), "h:mm")sectie naar behoefte aan.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen