Time Format in Excel to avoid mistakes

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 😉

Same value of 0.5 returns 12 hours

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🤔)

Whole number in Time

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.

Whole number to Date and Time format

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

Divide the value by 24

Then, you apply the Time format, you have the correct result 😍😎

Convert correctly to Time format

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)

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

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 😉

Same value of 0.5 returns 12 hours

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🤔)

Whole number in Time

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.

Whole number to Date and Time format

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

Divide the value by 24

Then, you apply the Time format, you have the correct result 😍😎

Convert correctly to Time format

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)

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *