Let’s take a look. Here we have a set of times in column B of our table. Let’s start off by copying these times to all columns, then adjust formats to match those shown in the table header. Let’s look first at the default time format in column C. This is the format you get when you apply the Time format using the menu in the ribbon. If we check this format in the Format Cells dialog, we see it listed first, with an asterisk. The asterisk means that this time format is controlled by the regional settings on the computer. If we change the time format in Windows, in the Region and Language control panel, we’ll see the change in Excel for cells that use this time format. This means that this time format may look different on different computers, depending on regional settings. If you need to ensure that the display of time is always the same, it’s better to use another format option. To set the time format indicated in columns D through H, we need to use the Format Cells dialog. We can set time to display in military format—without the AM or PM—with and without seconds. We can also use Format Cells to show a standard AM/PM time with and without seconds. Finally, we have the option to select a time format that includes a date. Because these times don’t contain a date component, Excel will display the first date in its date system. As a result, time formats that include a date only make sense when the cell contains both a date and a time.  

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.