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.

Table of Contents

## 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]

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.