Exceljet

Quick, clean, and to the point

How to create a custom time format

In this lesson, we'll look at how to create a custom time format. Excel provides a good selection of time time that can be used to assemble a variety of custom time formats.

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, 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, and add "Start: " in quotes to the front of the format. We can check the live preview to make sure 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 just 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 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. We can remove seconds, and add the letter "m" for minutes.

Course 
Core Excel
Author 
Dave Bruns