How to Input Time Duration in Excel

How to input time duration in Excel
Table of Contents

One thing I am really confused is that Excel doesn’t have a direct way of inputting time duration. I understand the chance of showing a time duration may be relatively rare but it surely isn’t zero. If you just go ahead and try to input a time duration in Excel, you will end up seeing it turns into a time rather than a time duration.

What if you really need to show the time duration in hours and minutes? There doesn’t seem like an easy way to do it.

In this article, I will show you how to input time in Excel quickly.

If you have the following questions, I suggest you reading this until the end.

  • How to enter time duration in Excel?
  • How do you enter hours and minutes in Excel?
  • How to delete hidden rows from entire Excel workbook?

You might also be interested in Excel Split Long Text into Short Cell Without Splitting Word.

Input time duration by changing “Number format”

Unfortunately, there isn’t a straightforward way to entering time duration in Excel. I genuinely hope that Excel can introduce a more direct way in terms of inputting time duration.

Luckily, we can achieve similar result by changing the number format of the cell.

Let’s take a look at the following example.

The example illustrate

What we would like to do

To input time duration instead of time

You might also be interested in How to prevent duplicate entries in Excel?

Step 1: Select cells

Since I would like to show cell B2 to D3 in a time duration format, I highlighted cell B2 to D3.

Step 2: “Home” > the black arrow in the “number” group > “More Number Formats”

Next, we are going to change the number format of selected cells. Bare in mind that changing number format only changed how the data is shown, instead of changing the data.

Step 3: Select “Custom” from the Category Bar > select “h:mm” > “OK”

The “Format Cells” window allow you to change the number format of the selected cells.

Here we are going to select “h:mm”, which means “showing only the hours and minutes”.

By default, Excel set the number format of your cells as “h:mm am/pm” when it thinks that you are trying to input a time.

By changing the number format to “h:mm”, you get rid of the “am/pm” so it looks like a time period rather than a time.


You might also be interested in 6 Ways To Converting Text To Number Quickly In Excel

Things to beware of

Even if it does seem like a time duration format, it isn’t.

If you pay attention to the formula bar, you will see that it still says “5:34:00 am”. The data itself hasn’t changed. The only thing that changed is how the data is shown.

Personally I don’t think that it is a big issue. I just want to make sure you are aware of that.

You might also be interested in How To Remove Digits After Decimal In Excel?

Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!

International Open Academy

Join Our Newsletters!