Time format in Excel

In Excel, working with the time is not difficult but you need to know few rules to avoid big mistakes. In this article you will see these rules and how to manage the time format.

Difference between date and time in Excel

In Excel, hours are always a fraction of a day. So it's decimal numbers

This rule is fundamental to avoid mistake in the calculation and display

  • Dates are whole number
  • Hours are decimal number

A whole number will never be understand as hours or minutes in a worksheet.

 

Visualize the differences

This document displays the same value, 8, with 3 different format.

  • First cell, 8 with the General format
  • Second cell, 8 with the Time format
  • Third cell, 8 with the Date format

For Excel, 8 is 8 days. So it's normal that when you convert in Time, the result is 00:00:00 for midnight

Now, when the format is Date & Time, the value 8 is understand as the 08/01/1900

Convert your whole number in fraction

If you have to convert an whole number in Time, you must divide that number by 24 (the number of hours in a day 😉).

=8/24 = 0,333333

Then, you apply the Time format an the cell displays 08:00:00 😍😎

There is also an easiest way to convert a whole set of whole number in fraction with paste special, option operation divide.

Time format

Customize Hours, Minute and second

In the article Date Format, we have seen how to customize the date and time with the dialog box Number Format.

To customize a time, you can use the following parameters.

  • h for the hour
  • m for the minute
  • s for the second

It is not possible to have a custom format just for the minutes.

The reason it's because m is by default the parameter for the month. So you can create a custom time format with, hour & minute or minute & second, but never minute alone.

Hour greater than 24H

If you want to display hour after 24H, you have to write the code between bracket

=[h]

Examples of the value 3.25 with différent formats

If you don't do that, Excel will always display a number of hours between 0 and 23 and the rest is convert in number of days.

Here 3.25 is 3 days and 6 hours or 78 hours. It depends of the format of the h.

 

 

All the Time format in one document

In the following document, you have a sum up of all the combinations of Time format.

Related posts


Permanent link to this article: https://www.excel-exercise.com/time-format-in-excel/


Leave a Reply

Your email address will not be published.