Hoe geef ik negatieve tijden correct weer in Excel?
Tijdsberekeningen in Excel kunnen lastig zijn wanneer u een latere tijd van een eerdere tijd aftrekt. Bijvoorbeeld: als u 12:20 aftrekt van 10:15, toont Excel een ######-fout in plaats van een negatieve tijdsnotatie, zoals te zien is in de schermafbeeldingen hieronder. Dit komt doordat Excel standaard alleen positieve tijdsnotaties ondersteunt binnen het standaard datumsysteem. Het oplossen van dit probleem is essentieel voor nauwkeurige planning, correcte urenregistratie en situaties waarin u negatieve tijdsduur of overuren moet weergeven.
➤ Geef negatieve tijden correct weer door het standaard datum systeem van Excel te wijzigen
➤ Geef negatieve tijden correct weer met formules
➤ Geef negatieve tijden weer met VBA-code (aangepaste Tekstformaat)
Negatieve tijd correct weergeven door het standaard datumsysteem van Excel te wijzigen
Bij het werken met tijdsberekeningen is een eenvoudige manier om negatieve tijdsnotaties weer te geven het overschakelen van de werkmap naar het 1904-datumsysteem. Hierdoor worden negatieve resultaten correct weergegeven (bijvoorbeeld ‘-2:05’) in plaats van foutmeldingen. Dit is handig wanneer u regelmatig negatieve duurperiodes moet tonen, zoals bij projectbeheer of ploegplanning.
Opmerkingen:
- Het overschakelen naar het 1904-datumsysteem verschuift alle bestaande datums in de werkmap met ongeveer 1.462 dagen (~4 jaar). Controleer na de wijziging andere datumvelden.
- Deze instelling is specifiek voor de werkmap (heeft geen invloed op andere bestanden).
- Bij samenwerking dient u zich ervan bewust te zijn dat gebruikers met het standaard 1900-datumsysteem mogelijk andere resultaten zien wanneer werkmappen worden gemengd.
- U kunt het datumsysteem van 1900 en dat van 1904 niet tegelijkertijd in dezelfde werkmap gebruiken.
1. Open het dialoogvenster Excel-opties. In Excel 2010/2013 gaat u naar Bestand > Opties. In Excel 2007 klikt u op de Office-knop en kiest u Excel-opties.
2. Klik in Excel-opties op Geavanceerd. Vink onder Bij het berekenen van deze werkmap het selectievakje Gebruik 1904-datumsysteem aan. Zie de onderstaande schermafbeelding:
3. Klik op OK. Negatieve tijdsverschillen worden nu correct weergegeven, zoals hieronder te zien:
Tips:
- Als het behouden van historische datumnauwkeurigheid essentieel is, overweeg dan alternatieve methoden—zoals aangepaste tekstuitvoer via een formule of VBA—in plaats van het datumsysteem aan te passen.
- Documenteer de wijziging van het datumsysteem in de werkmap (bijvoorbeeld op een „LeesMij”-werkblad) om verwarring onder andere gebruikers te voorkomen.
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.
Negatieve tijd correct weergeven met formules
Als uw workflow het wijzigen van het datumsysteem niet toestaat – bijvoorbeeld vanwege compatibiliteit of rapportage-eisen – kunt u negatieve tijdsweergave eenvoudig realiseren met formules. Deze methode toont negatieve duurperiodes als tekst, zodat u duidelijke visuele resultaten krijgt zonder dat Excel een foutmelding geeft. Deze aanpak wordt veel toegepast bij tekorten in gewerkte uren, vertragingen in projecten of het vergelijken van verstreken tijd.
1. Voer de tijden die u wilt berekenen in twee afzonderlijke cellen in (bijvoorbeeld A1 en A2). Typ in een lege cel de volgende formule om het negatieve verschil te berekenen en op te lossen:
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Hierbij vertegenwoordigen A1 en A2 de twee tijdsnotaties. Pas de celverwijzingen aan naar uw gegevens. Deze formule levert een tekstresultaat op, zodat u eventuele mintekens ziet—zelfs als Excel bij de berekening normaal gesproken een foutmelding zou geven.
2. Druk op Enter. Het resultaat verschijnt als een opgemaakte tijdsreeks, met een minteken als het negatief is, zoals hieronder getoond:
Alternatieve formule:
U kunt ook deze formule gebruiken, die voorwaardelijk een minteken weergeeft wanneer het resultaat negatief is:
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)In deze opzet is A2 de kleinere of eerdere tijd en A1 de latere tijd, maar pas deze naar wens aan voor uw berekeningsrichting. Deze methode levert een tekstresultaat op bij negatieve waarden, wat handig is voor rapportage. Houd er echter rekening mee dat de resultaten niet als tijden kunnen worden opgeteld zonder extra conversie.
Opmerkingen:
- De resultaten zijn tekst, geen echte tijdsnotaties, dus verdere tijdsberekeningen werken niet tenzij u de uitvoer opnieuw converteert naar een tijdnotatie.
- De formule-aanpak is werkmaponafhankelijk, ideaal voor delen en voorkomt conflicten met globale instellingen.
- Let op de juiste celrichtingen om logische fouten te voorkomen, zoals het aftrekken van de eindtijd van de begintijd, wat tot een negatief resultaat leidt.
Negatieve tijd weergeven met VBA-code (aangepaste Tekstformaat)
Een andere effectieve manier om negatieve tijden in Excel te verwerken, is het gebruik van een VBA-macro. Dit is vooral handig als u negatieve tijdsberekeningen in uw werkblad naadloos en automatisch wilt detecteren en weergeven – zelfs bij grote hoeveelheden gegevens – zonder systeeminstellingen aan te passen of voor elke cel afhankelijk te zijn van formules. Deze methode is ideaal voor gevorderde gebruikers die regelmatig tijdsgegevens invoeren, zoals in ploeglogboeken, en voorkomt de ######-fout door negatieve tijdsresultaten om te zetten naar aangepaste tekstreeksen (bijvoorbeeld met een minteken).
Voordelen: Batchverwerking van geselecteerde cellen is mogelijk, de originele gegevens blijven behouden, resultaten kunnen naar wens worden opgemaakt en het datumsysteem van Excel blijft ongewijzigd.
Nadelen: Vereist ingeschakelde macro’s en is niet geschikt als u de oorspronkelijke Excel-tijdsnotaties wilt behouden voor verdere berekeningen naast weergave.
Aandachtspunten en tips:
- Maak altijd eerst een back-up van uw bestand voordat u VBA-scripts uitvoert.
- Deze oplossing zet negatieve tijdsresultaten om in tekst. Gebruik voor toekomstige berekeningen altijd de oorspronkelijke numerieke gegevens, niet het door de macro gegenereerde weergaveresultaat.
- Macro’s kunnen zijn uitgeschakeld door uw IT-beleid. Schakel macro’s in Excel in om deze oplossing te kunnen gebruiken.
1. Klik in Excel op Ontwikkelaar > Visual Basic. Klik in het VBA-bewerkervenster op Invoegen > Module en 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. Klik op de knop 
Uitvoeren in de VBA-editor of druk op F5. Er verschijnt een dialoogvenster waarin u het bereik met uw tijdsverschillen kunt selecteren (zelfs die met de)######-fout).
De macro scant automatisch het geselecteerde bereik. Voor elke cel met een negatieve tijdsnotatie (die normaal)###### zou tonen) wijzigt de macro de cel naar het tekstformaat en converteert de waarde naar een leesbaar formaat, zoals „-2:05”. Positieve tijdsresultaten blijven ongewijzigd. Als u terug wilt naar de normale tijdnotatie, herstelt u de oorspronkelijke celwaarden of gebruikt u een herstel-/ongedaanmakingsactie.
Probleemoplossing en verdere tips:
- Als u een bereik selecteert zonder tijdsverschillen—bijvoorbeeld lege cellen of waarden die geen tijdrepresentatie bevatten—slaat de macro deze veilig over.
- Als er geen cellen zijn geselecteerd, vraagt de macro om een bereik en laat deze overige gegevens onaangetast.
- Controleer na uitvoering altijd de opmaak: de betreffende cellen zijn nu als tekst opgemaakt, waardoor de oorspronkelijke tijdsberekeningen niet meer werken totdat u deze opnieuw instelt.
- Wilt u de macro aanpassen voor andere weergaveformaten of langere uur-minuut-seconde-structuren? Pas dan het
Format(Abs(Cell.Value), "h:mm")-gedeelte naar wens aan.
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