Look Who it Was Random video

Excel How To See Negative Time In Excel / or Time over 24 Hours - Episode 983

1,466 views · Published 1 April 2009 · 3:53 · Indexed 22 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Showing Negative Time in Excel. Also showing time in excess of 24 hours in Excel. 

Welcome back to the MrExcel netcast! In today's episode, we're tackling a question sent in by Bill about time calculations in Excel. Bill is tracking his vacation time and has run into two situations that need solving. The first problem is that as he starts earning more vacation time, the calculation stops working. This is because Excel's traditional time format only shows the hours in excess of a full day. But fear not, there's a simple solution! By using the custom time format and adding square brackets around the "h", we can force Excel to show more than 24 hours. However, there's an even easier way to do this - by using the built-in format for showing more than 24 hours, labeled as "37 hours". This will solve Bill's first problem and allow him to accurately track his vacation time.

But wait, there's more! Bill's second problem is that his company allows employees to borrow ahead on vacation time, up to a certain amount. However, when he enters this borrowed time, the calculation stops working altogether. This is because Excel does not allow for negative time. But don't worry, there's a hidden solution buried in the Excel options. By going to the Advanced tab and checking the box for "use 1904 date system", we can trick Excel into allowing negative time calculations. This is because the 1904 date system is used for Macintosh computers, which cannot recognize dates before January 1st, 1904. This trick will allow Bill to accurately track his borrowed vacation time without any issues.

One important thing to note is that if there are any dates entered in the spreadsheet, they will also be affected by the 1904 date system. So, it's important to either enable this option before entering any dates or double-check all dates after enabling it. I want to thank Bill for sending in this question and I hope this solution helps him and anyone else who may be facing similar time calculation issues. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to join us next time for more Excel tips and tricks. And remember, if you ever find yourself asking "Where's it Wednesday?", just know that we're here to help you find the answer. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) First problem: tracking vacation time 
(00:28) Second problem: using negative time in Excel
(01:12) Solution for showing times in excess of 24 hours
(01:44) Solution for negative time
(03:08) Caution when using 1904 date system 
(03:20) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
how to do negative times in excel
how to show over 24 hours in excel time
how to see negative time in excell
how to allow negative time in excel
how to enter negative time in excel
how to display a negative time excel
how to show negative time difference in excel
how to show negative time on excel
how to show negative times excel
how to make excel show negative time
how to get excel to show negative time
how do you get excel to diplay negative time
how to get negative time in excel
how to make negative time excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152326/

Bill from Washington asks how to track vacation time, specifically times greater than 24 hours and times less than 0 hours. This requires two different tricks. Episode 983 shows you how.

More from this channel