Home » Function » Date » Time Format in Excel

Time Format in Excel

Reading time: 3 minutes
Last Updated on 05/12/2021 by Frédéric LE GUEN

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 ❗❗❗

A very good example of used is the technique to split date and time to 2 different cells.

Split Date and Time with Excel

Why I can't convert to Time format

For instance, in this situation, you have whole number in column A. But when you apply the Time format, all the cells returned 00:00:00 🤔

Whole number in Time

But this is absolutely normal. In Excel, a whole number is a day (and not a time). Now, if you convert to Date & Time format you have this result

Whole number to Date and Time format

How to Convert a whole 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) 😍😍😍

Time format 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

Hours over 24 hours

By default in Excel, Time values is between 00:00:00:and 23:59:59

But there is a trick to display times over 24 hours 😉

Excel displays time over 24H

Tenth, hundredth, thousandth

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

Format for thousandth

Leave a Reply

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

Time Format in Excel

Reading time: 3 minutes
Last Updated on 05/12/2021 by Frédéric LE GUEN

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 ❗❗❗

A very good example of used is the technique to split date and time to 2 different cells.

Split Date and Time with Excel

Why I can't convert to Time format

For instance, in this situation, you have whole number in column A. But when you apply the Time format, all the cells returned 00:00:00 🤔

Whole number in Time

But this is absolutely normal. In Excel, a whole number is a day (and not a time). Now, if you convert to Date & Time format you have this result

Whole number to Date and Time format

How to Convert a whole 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) 😍😍😍

Time format 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

Hours over 24 hours

By default in Excel, Time values is between 00:00:00:and 23:59:59

But there is a trick to display times over 24 hours 😉

Excel displays time over 24H

Tenth, hundredth, thousandth

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

Format for thousandth

Leave a Reply

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