The time formatting we reviewed in the last Excel Video has a catch we’ll discuss in Excel Video 169. As long as there are less than 24 hours (or 60 minutes or 60 seconds), the formatting we introduced in the last Excel Video works just fine. As soon as you go over 24 hours, Excel adds one to the count of days and starts hours back over at zero. You’ll see in the video that the total elapsed time is 51 hours, but the h:mm formatting pattern we introduced last time only shows 3 hours since Excel converts 48 of the 51 hours to 2 days.
There are two ways to make sure you see the total 51 hours instead of just 3 hours. First, you can put brackets around the h in the format. The custom format [h]:mm will count total hours elapsed and give you the 51 hours you’re looking for. The other solution is to show the next larger time category, days in this case. The custom format d:h:mm:ss will show days, hours, minutes and seconds. In our example, you’ll see 2:3:15:00.
If your time calculations will never exceed 24 hours (or 60 minutes or 60 seconds), you can use the h:mm custom format and be just fine. If there’s a chance you’ll exceed 24 hours (or 60 minutes or 60 seconds), just be sure to put the relevant piece of the custom time format in brackets or create a custom format to show the next larger time category.
We’ll do one more video on time functions. Stay tuned next time and we’ll calculate hours, minutes, and seconds.
