# Time Format in Excel to avoid mistakes

Last Updated on 04/11/2023
Reading time: 2 minutes

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 (ALWAYS)

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, ...)

For instance, in the following picture, we have the value 0.5 in a cell. If we change the format number to the Time format, this time we have 12 hours. This is logic because 12 hours is the middle of the day, so 0.5 day 😉

## What about using Integer?

Now, if you have integer numbers in your cells, and you apply the Time format, all the cells display 00:00:00 (WHY🤔)

It's not a mistake. Excel understands these values are days and not hours. You can see that if you change the format of the cells in Date and Time.

## How to Convert an Integer Number in Time

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, you have the correct result 😍😎

If the content of your cell is a seconde and not hour, then the formula is

=8/(24*60*60)

## 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 (option divide)

# Time Format in Excel to avoid mistakes

Reading time: 2 minutes
Last Updated on 04/11/2023

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 (ALWAYS)

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, ...)

For instance, in the following picture, we have the value 0.5 in a cell. If we change the format number to the Time format, this time we have 12 hours. This is logic because 12 hours is the middle of the day, so 0.5 day 😉

## What about using Integer?

Now, if you have integer numbers in your cells, and you apply the Time format, all the cells display 00:00:00 (WHY🤔)

It's not a mistake. Excel understands these values are days and not hours. You can see that if you change the format of the cells in Date and Time.

## How to Convert an Integer Number in Time

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, you have the correct result 😍😎

If the content of your cell is a seconde and not hour, then the formula is

=8/(24*60*60)

## 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 (option divide)