Time Formats in Excel

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

Difference between date and time in Excel

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

This rule is fundamental to avoid mistakes in calculation and display.

  • Dates are whole numbers
  • Hours are decimal numbers

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

Visualize the differences

This document displays the same value, 8, in 3 different formats.

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

In Excel, 8 is 8 days. So it's normal that when you convert to Time, the result is 00:00:00 for midnight because it only shows the time and not the date.

When the format Date & Time is used, the value 8 is understood as 08/01/1900, which is 8 days after 01 January 1900.

Convert a whole number to a fraction

If you have to convert a whole number to 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 to the cell and it displays 08:00:00 😍😎

There is also an easier way to convert a set of whole numbers to fractions with paste special, option operation divide.

Time format

Customize hours, minutes and seconds

In the article Date Format, we saw how to customize the date and time using the dialog box Number Format.

To customize a time, you 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 is because m is by default the parameter for month. So you can create a custom time format with, hour & minute or minute & second, but never minute alone.

Hours greater than 24 hours

If you want to display hours after 24 hours, 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 you use for h.

All Time formats in one document

In the following document, you have to sum up of all the combinations of the 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.