# 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 is necessarily decimal numbers.

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

• Dates are whole numbers (like 1, 2, 3, ....)
• Hours are decimal numbers (like 0.5, 0.33333, ...)

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 😍😎

## Technique with Paste Special - Divide

DON'T waste your time. There is a technique to divide all your numbers by 24 in 2 steps.

The secret is to use paste special with the option 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

You can also display the tenth, hundredth and thousandth. But for that, you have to add 0.

### Hours over 24 hours

By default, if a time calculation exceed 23:59:59, Excel will display in number of days and hours.

To avoid this, go to this article to know the technique to display hours over 24 hours