Formatting Time in Excel

How to Format Time in Excel? (Step by Step)

As we can apply the time format for any decimal or fractional value.

So, this will differentiate the AM and PM times.

Understanding the Time Format Code

As we have learned above, excel time format code is hh:mm:ss. Let me explain this code in detail now.

• hh: This time code represents the hour part of the time in double-digit value. For example, in the above example, our time value showed as 06; if you mention a single “h,” then the hour part will be only 6, not 06.
• mm: This code represents minute part of the time in double-digit value.
• ss: This will represent the second part of the time.

If you don’t want to see the “seconds” part from the time, then apply only the “time and minute” part of the code.

We can also customize the time. For example, 0.689 is equal to the time of 04:32:10 PM.

Instead of showing like the below, we can modify it as “04 Hours, 32 Minutes, 10 Seconds”.

We get the following result.

For this, we enter the below custom time code.

hh “hours”, mm “Minutes”, ss “Seconds” AM/PM

So this will display the time as we have shown above.

Different Formatting Technique for More than 24 Hours’ Time

Working with time could be tricky if you don’t know the full formatting technique of time because if you want to enter the time more than 24 hours, we need to employ different formatting code.

For example, Mr. A was a sales manager, and below are his call records for the past 5 days.

Now he wants to calculate his total call duration of the week.

So let’s sum all the days’ in time format of cell B7.

OMG!!! We got the total as 03:20:10, which is absolutely wrong.

This is a real-time experience of my own. By looking at the data, we can easily say the total duration is more than 03:20:10, so what is the issue with it???

The issue is when the summation or time value exceeds 24 hours, we need to give slightly different time formatting codes.

For example, let us select the call duration time and see the status barStatus BarAs the name implies, the status bar displays the current status in the bottom right corner of Excel; it is a customizable bar that can be customized to meet the needs of the user.read more to see the sum of the selected values.

So, the total in the status bar is 27:20:10, but our SUM functionSUM FunctionThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more has returned 03:20:10.

To understand this better, copy the result cell and paste specialPaste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more as values in another cell.

We get the value as 1.13900463. i.e., 1 Day, 20 minutes 10 seconds.

As I told, the time value is stored as serial numbers from 0 to 0.9999; since this total is crossing the fraction mark, we are getting this error sum.

So for this, we need to apply the time formatting code as “[hh]:mm:ss.”

We get the following result.

Same formula we just have changed the time format to [hh]:mm:ss.

Things to Remember

• Time is stored as decimal values in excel.
• The date and Time are combined in excel.
• When the time value exceeds 24 hours, then we need to enclose the time format code of the hour part inside the parenthesis. i.e. “[hh]:mm:ss”.

Recommended Articles

This has been a guide to formatting time in excel. Here we discuss how to format time in excel along with practical examples and downloadable excel templates. You can learn more from the following articles –

• Format Text in Excel
• Add Time in Excel
• TimeValue in VBA
• Subtract Time in Excel