Formatting Time in Excel

Formatting Time in Excel – Table of Contents

• Change Date and Time Format in Excel
• How to Format Time In Excel?
• Understanding Time Format Code
• Formatting Techniques

How to Format Time in Excel?

In excel DATE & TIME are stored as serial numbers, we need to apply formatting for them to display a date and time. Let’s start with a simple example of formatting time in excel, in cell A1 I have entered the value as 0.25 as shown below.

If we apply time format in excel then we will get time as below.

(We will show you how to apply time format in excel).

So, the value 0.25 is equal to the time 06:00:00 when we apply the time format. Similarly, look at the below number in cell A2.

I will apply the date format to this number and see the end result.

So, the serial number 43696 is equal to the date 19-Aug-2019 according to date format in excel.

Now let’s combine these two numbers i.e. 0.25 & 43696.

Now if we apply date and time format to this number.

We get the following result.

As we can see above we can segregate the combination of date and time into two parts. So full serial number represents the DATE portion and the decimal value represents the time portion.

Time value will be stored from 0 to 0.9999, in this case 0.25 is equal to 6:00:00, similarly 0.5 is equal to 12:00:00 and 0.9999 is equal to 23:59:59.

Examples to Format Time in Excel

Below are examples of formatting time in excel.

As we can apply the time format for any decimal or fractional value above, now let’s learn how to apply time format in excel for 0.25 value.

Select the cell, right-click and choose FORMAT CELLS option.

Now we can see below the “Format Cells” window.

Choose the TIME category.

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

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

By using the TIME format category, we can also use the “Custom” category as well to modify the time format.

I have applied the time formatting code as “hh:mm: ss” so, my time shows the preview as 06:00:00. This time format code will show the time in 24-hour format, so if you don’t want to see the 24-hour time format then enter AM / PM separator.

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 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 “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 in excel 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 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 code.

For example, let us select the call duration time and see the status bar to see the sum of the selected values.

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

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

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

As I told 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.
• 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 –

• Excel Percent Change | Examples
• Create Excel Step Chart
• How to Format Text in Excel?
• Formula of Time in Excel
• How to Add Time in Excel?
• TimeValue in VBA
• How to Subtract Time in Excel?