Let’s take a look. Let’s look first at the Time code reference table. This table shows the time codes available for custom time formats. There are codes for hours, minutes, and seconds with and without leading zeros. There are also two options for AM/PM code, and square brackets, which we’ll get to in a moment. Now let’s look at Table A. In the left column, we have a list of times. The other columns are meant to hold two custom time formats. Let’s start by copying the times across the entire table. For column C, we just need to make a few modifications to the existing format. In the Format Cells dialog, we click Custom to start. First, we can remove the locale code at the start, and the text format code at the end. We don’t need those. Then, we remove seconds and replace AM/PM with A/P. For column D, we start the same way. Format Cells, then Custom. Then, we remove AM/PM code and add “Start” with a colon in quotes to the front of our format. We can check the live preview to make sure that our format looks correct. Now let’s look at Table B. In this table the first column is a start time and the second column is an end time. Column D is mean to display duration in minutes. Because times are just numbers, we can subtract the start time from the end time to calculate a duration. What we get isn’t quite what we want—we want a duration in minutes, not a new time of day. The problem is the format. Let’s try the Custom format mm:ss. This is closer, but note that when we cross over 60 minutes, the minutes roll over. In the second row, we should see 75 minutes, not 15. The solution is to wrap the mm in square brackets. This stops minutes from rolling over when they reach 60. We can even refine this format to get a more custom look. For example, we can remove seconds, and add the letter “m” in quotes for minutes.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
