Formatting Time in Excel

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

Now, let us learn how to use the time format in Excel for the 0.25 value.

1. We must first select the cell. Then, right-click and choose FORMAT Cell excel.

   
   

2. Now, we can see below the Format Cells window. From there, choose the Time category.

   
   

3. Now, we can see all the time types available for this value as per the location setting.

   
   

4. We can see the preview of the selected cell time format. Choose any of the ones to see a similar time in the cell.

   
   

5. Using the Time format category, we can also use the Custom category to modify the time format.

   
   

6. We have applied the formatting code as hh:mm:ss, so our time shows the preview as 06:00:00. This time format code will show the time in 24-hour format. So if we do not want to see the 24-hour time format, we must enter the AM / PM separator.

   
   

So, this will differentiate the "AM" and "PM" times.

As we learned, the 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 we mention a single "h," the hour part will be only "6," not "06."
• mm: This code represents a minute part of the time in a double-digit value.
• ss: This will represent the second part of the time.

If we do not 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" equals the time of "04:32:10 PM."

So, 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 must 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.

Working with time could be tricky if we do not know the full formatting technique because if we want to enter the time more than 24 hours, we need to employ a different formatting code.

For example, Mr. A was a sales manager. Below are his call records for the past five days.

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

So, let us sum all the days in the time format of cell B7.

We got the total as "03:20:10," which is wrong.

It is a real-time experience. Looking at the data, we can easily say the total duration is more than "03:20:10," so what is its issue?

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 bar to see the sum of the values chosen.

So, the total in the status bar is "27:20:10," but our SUM function has returned "03:20:10."

To understand this better, we must copy the result cell and use paste special as values in another cell.

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

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

So for this, we need to apply the time formatting code as ":mm:ss."

We get the following result.

Same formula, we have changed the time format to :mm:ss.

• Time is stored as decimal values in Excel.
• The date and time are combined in Excel.
• When the time value exceeds 24 hours, we must enclose the time format code of the hour part inside the parenthesis, “:mm:ss.”