How to format time in Excel is a common query among Excel users. As you read this blog, I presume you also have the same question.

As Excel is used as a database of numbers and text strings, the software offers a number of formats to store data. You can take time for example. If you want to include time data in your Excel spreadsheet, you can do that in different formats. Though you can format the time data in your Excel in different patterns, some people find it hard to implement the time formats in Excel.

Did you just receive multiple Excel files from a colleague and did the files use different formats for time? In that case, you need to make sure that the time formats used in these files are the same to avoid any inconsistency during data merge. To do that, you must know how to format time in Excel.

Are you also facing difficulty in formatting time in spreadsheets? Reading this article till the end could resolve your problem and make you aware of the top methods of Excel time data formatting.

Reasons to Format Time in Excel

• The most important reason to format time is to maintain consistency among all the time data of a spreadsheet for accurate data analysis and calculations.
• Using the most common format used by a person or an organization ensures more readability and quick interpretation.
• Excel can sort time values chronologically or reverse chronologically. All the time values should be in the same format for sorting and filtering.
• If you format time in Excel in a human-readable format, your clients or stakeholders will be able to understand it easily.
• A properly formatted time data in Excel allows you to understand time duration and elapsed time.
• When importing or exporting data between Excel and other applications, maintaining consistent formatting ensures that the data is correctly interpreted by both systems.
• A consistent and intelligible format ensures accuracy for any mathematical operations and calculations involving time.
• Data formatting functions as a form of data validation that prevents errors and ensures data integrity.
• Apart from the above reasons, formatted time values are essential for accurately representing time-based trends while creating charts or graphs.

Understanding How Excel Processes Time

Microsoft Excel processes time entries as a decimal fraction of a day. The integer part of the decimal represents the number of days, and the fractional part represents the time within that day.

For instance, 0.5 in Excel means 12:00 PM, as it represents half of a day. Since time is processed as decimal values, you must appropriately format time entries to create meaningful data analysis.

Suppose, there are a few elapsed time entries in your Excel worksheet, like 3:50:06, 2:48:45, 7:50:50, 8:45:50, and 1:45:32.

If you go ahead and try to SUM these numbers in Excel, you’d get 1:01:03 AM, which is incorrect. The correct SUM would be 25:01:03. You can only get the correct value if you apply the elapsed time formatting in Excel, which is [h]:mm:ss.

There are various methods to format time entries in Excel to visualize time the way you want. The best way is using the Format Cells dialog and applying custom codes as mentioned in the following table:

This time format coding system enables you to perform calculations and manipulate time-based data efficiently.

Now, find below the time formatting methods you should practice to excel in your Microsoft Excel game:

Format Time in Excel Using the Number Group

Suppose you’ve got the following Excel dataset where time entries are in decimal values:

To convert the entries in the time column (Call Time) to time data as hours: minutes: and seconds, highlight the column or the cell range.

Go to the Number commands block on the Home tab of the Excel ribbon menu. There, click the General drop-down list and choose Time from the context menu that shows up.

Excel will convert the decimal values to time entries like this 3:05:45 AM.

Now, if you don’t want AM or PM at the end of the time entry, again go to Home > Number > General drop-down list.

There, click on the More Number Formats option. You’ll see a Category list in the Format Cells dialog.

Select the Time category and select the 13.30.55 time code from the right-side menu. Excel will remove AM and PM entries from your time dataset.

Format Time in Excel Using a Shortcut

There’s a handy shortcut you can use to quickly format one cell, a cell range, or a column or row using the Ctrl + 1 keys.

Suppose, you’d like to change the existing time formatting of a few time entries on your worksheet. The existing time entries are in elapsed time format and you want to view those as AM/ PM.

Highlight the source time entries on your worksheet. Then, press the Ctrl + 1 keys together to bring up the Format Cells dialog. There, go to the Number > Custom category and choose the h:mm:ss AM/PM code.

Excel shall convert the elapsed time entries to AM/ PM format.

Format Negative Time Entries in Excel

Suppose, various offices of your business are supposed to invest at least 5 hours in customer support calls. You’ve created the following Excel format to find out the deficit or surplus calling hours.

Now, you extract the raw calling hours data from a calling software for all the office locations and feed that into the Call Time column.

As you do this, the Surplus/Deficit column subtracts Expected time values from Call Time values. In places, where the Call Time is smaller than the Expected time, you’ll see ######### instead of the negative time values.

If you want to format a negative time entry differently select the #-code entries in column E. Press the Ctrl + 1 keys to bring up the Format Cells dialog box.

There, select a suitable time format, like [h]:mm:ss and modify it to [h]:mm:ss;"Deficit". Press OK to apply the custom time formatting.

Excel will only show positive time values from the subtraction process. For negative values, it’ll show the Deficit text.

Format Time to Decimals in Excel

When you’ve got an Excel worksheet with time entries in hh:mm:ss format and want to convert those into decimal values, you just need to reverse the time formatting in Excel.

Simply, highlight the time entries on your worksheet. Press the Ctrl + 1 keys to bring up the Format Cells dialog.

There, click on the Number tab and choose the General category. You should already see the output inside the Sample field on the right-side menu.

Click OK on the Format Cells dialog to apply the changes. You should now see that Excel has converted the hh:mm:ss time entries to decimal values.

Format Time Entries to Show the Millisecond Place

Suppose, you’ve imported time entries from another software into Microsoft Excel. You know that the time entries contain the millisecond place along with hour, minute, and second. However, you’re unable to see that in Excel.

To resolve this problem, you must use a custom time formatting in Excel. First, highlight the time entries on your Excel worksheet. Then, press Ctrl + 1 to bring up the Format Cells dialog.

On the Format Cells dialog, select the Custom category under the Number tab. Choose the h:mm:ss time format code and modify it to h:mm:ss.000. Click OK to apply the new time formatting.

You should now see the millisecond values of the time entries on your worksheet.

Convert Time Entries to 12/24-Hour Format Using the TEXT Function

When you need to convert a given time format into a 12-hour or 24-hour format, you can use the TEXT function.

For instance, you’ve got the above Excel dataset where the time entries are in 24-hour format. However, due to regulatory compliance requirements or organizational policy, you must convert the 24-hour time format to a 12-hour format.

Go to the column where you want the 12-hour time format. Give it a column header like 12-Hour. In the second row of the column, enter the following formula:

=TEXT(A2,"hh:mm:ss AM/PM")

Hit Enter and Excel will convert the time input into a 12-hour format.

To apply the same time formatting to all other time entries in the reference column, drag the fill handle from the first calculated cell up to the mark where data exists in the adjacent column.

If the input time entries are in 12-hour format, then use the following formula to convert those to 24-hour format:

=TEXT(A2,"hh:mm:ss")

Format Time in Text Format to Decimal in Excel

When you get time entries in Excel as text strings rather than numbers, it’s challenging to subject this temporal data to further data analysis. You must convert the time in text format to decimal values. Once you get the decimal values of time entries, you can easily convert those to hh:mm:ss time format.

In this scenario, you can use the TIMEVALUE function in Excel. This function has been available since Excel 2007 desktop application so it also has backward compatibility.

The TIMEVALUE function enables you to enter a text string of time and the function will convert the input to the corresponding decimal value.

Now, go to your worksheet and choose a column under which you want to populate decimal values for the corresponding time entries as text strings in the adjacent column. Give the selected column a header, like Decimal Values (column B).

Select the first cell below the column header and enter the following formula into it:

=TIMEVALUE(A2)

Hit Enter to get the decimal value of the first time entry of your dataset.

Now, use the fill handle and drag it down the column to populate decimal values for other time entries in the adjacent column.

Now, highlight all the decimal values in column B. Now, go to Home > Number group > and apply the Time formatting by clicking the General drop-down list. You should get time entries in AM/ PM.

Format Time Entries in Excel That Are More Than 24 Hours

Suppose, you’ve got an Excel dataset of project completion time. When you want to sum the time entries you may get incorrect results if you don’t apply the correct time formatting to the source dataset.

First, you must apply the elapsed time format to the time entries you’re using as inputs as shown in the above image. Simply, call the Format Cells dialog and choose the [h]:mm:ss time format code from Number > Category > Custom.

Now, you can easily use the SUM formula to find out the total time taken to complete all the projects in the Excel worksheet.

If you want to convert the total project time value into a date format, use this time formatting code in the Format Cells > Custom category:

d "day" h:mm:ss

Format Time in Excel Using Excel VBA

Formatting time entries in Excel is effortless if you can use Excel VBA scripts. The following VBA script will transform decimal time entries in B2:B6. For the transformation, Excel will use the [h]:mm:ss time formatting code.

Sub ConvertDecimalToTime()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet7")
    
    ' Loop through the cells in column B
    For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        ' Convert decimal to time format in column C
        cell.Offset(0, 1).Value = TimeSerial(0, 0, cell.Value * 86400) ' 86400 seconds in a day
        cell.Offset(0, 1).NumberFormat = "[h]:mm:ss"
    Next cell
End Sub

Here’s how to customize the above-mentioned VBA script:

• Replace the code Sheet7 with your own worksheet name.
• If the decimal time entries are in a different cell range, enter that in place of B2:B6.
• Excel VBA will populate the result in the column to the right of the selected cell range.
• Modify the code elements cell.Offset(0, 1) to populate the results in a different column than the adjacent one.

Wondering how to use the VBA script in your worksheet? It’s quite easy if you read this article on how to use the VBA code you find online.

Conclusions

While documenting data, users need to add time to an Excel file. However, there are a number of formats available for enlisting time. While some people display time in a 12-hour format, others prefer to use a 24-hour format.

If your organization follows one particular format for time and receives an Excel file containing time in a different format, there should be a change of format in the Excel file. For that, you must know how to format time in Excel.

Follow the techniques mentioned in this blog, and you’ll be able to format time in an Excel spreadsheet into the format of your choice. If you know any better method, don’t forget to tell us in the comment section. You can also share your experience of implementing these methods with your fellow readers.