Time Format in Excel to avoid mistakes

Time Format in Excel to avoid mistakes
Last Updated on 30/04/2024
Reading time: 2 minutes

Learn how to manage Time Format in Excel to avoid mistakes. Since manipulating Date and Time can be tricky, calculations might be inaccurate.

How to write a Time in a cell?

In Excel, when you want to write a time in a cell, you MUST split the hours, minutes, and seconds with a colon (:). Automatically, Excel will apply the Time format to your values.

Write time with colon between hour minute second

But in most cases, it doesn't work. 🤔

But, usually, people don't write time with the colon sign. Most people just write the value of the hours with an integer number. Then, from the ribbon, they try to display the value in Time format and .... it doesn't work. In this situation, Excel will display 00:00:00 for each cell.

Whole number in Time
  • It's not a mistake!!!!
  • You have integer numbers in Column A.
  • Excel understands these values are days and not hours (hours are decimals)
  • You can see that if you change the format of the cells in Date and Time.
  • 8 has become the 08 January 1900; 8 days after the very first day known by Excel (01/01/1900)
Whole number to Date and Time format

In Excel, hours are always a fraction of a day.

  • 0 it's Midnight
  • 0.5, it's half-day, so it's 12:00:00
  • 0.33333, it's the third of a day, so 08:00:00
  • 0.99999, it's 23:59:59

How to Convert an Integer Number in Time

If you are in this situation, you MUST convert your values. And there are 2 ways to do that

  • Using the TIME function of Excel (easiest solution)
  • By doing formula
    • Divide hours by 24 =hours/24
    • Divide minutes by 24*60 =minutes/(24*60)
    • And for the seconds =seconds/(24*60*60)

Custom Time format in Excel

Once you've entered the correct time values in your cells, as Excel requires, you can customize the format using the three components of time.

  • h for hour
  • m for minute
  • s for second

You can combine each component to display you time in many different ways

  • hh:mm:ss => 08:35:14
  • hh:mm => 08:35
  • mm:ss => 35:14 (only minutes and seconds)
  • hh:mm AM/PM => 08:35 AM
  • [h]:mm => 27:13 (to display over 24 hours)
  • But you can't write mm alone (because Excel understands that it's for Months)
Examples of custom code for Time format

Where to write this Code?

  1. Go to Dropdown list of the number format
  2. Click on the last option More Number Formats
Custom Number Format
Custom Number Format
  1. In the dialog box, select the Custom Category.
  2. In the text box, write your code
  3. Click on OK
Where to write the custom time format code

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 30/04/2024

Learn how to manage Time Format in Excel to avoid mistakes. Since manipulating Date and Time can be tricky, calculations might be inaccurate.

How to write a Time in a cell?

In Excel, when you want to write a time in a cell, you MUST split the hours, minutes, and seconds with a colon (:). Automatically, Excel will apply the Time format to your values.

Write time with colon between hour minute second

But in most cases, it doesn't work. 🤔

But, usually, people don't write time with the colon sign. Most people just write the value of the hours with an integer number. Then, from the ribbon, they try to display the value in Time format and .... it doesn't work. In this situation, Excel will display 00:00:00 for each cell.

Whole number in Time
  • It's not a mistake!!!!
  • You have integer numbers in Column A.
  • Excel understands these values are days and not hours (hours are decimals)
  • You can see that if you change the format of the cells in Date and Time.
  • 8 has become the 08 January 1900; 8 days after the very first day known by Excel (01/01/1900)
Whole number to Date and Time format

In Excel, hours are always a fraction of a day.

  • 0 it's Midnight
  • 0.5, it's half-day, so it's 12:00:00
  • 0.33333, it's the third of a day, so 08:00:00
  • 0.99999, it's 23:59:59

How to Convert an Integer Number in Time

If you are in this situation, you MUST convert your values. And there are 2 ways to do that

  • Using the TIME function of Excel (easiest solution)
  • By doing formula
    • Divide hours by 24 =hours/24
    • Divide minutes by 24*60 =minutes/(24*60)
    • And for the seconds =seconds/(24*60*60)

Custom Time format in Excel

Once you've entered the correct time values in your cells, as Excel requires, you can customize the format using the three components of time.

  • h for hour
  • m for minute
  • s for second

You can combine each component to display you time in many different ways

  • hh:mm:ss => 08:35:14
  • hh:mm => 08:35
  • mm:ss => 35:14 (only minutes and seconds)
  • hh:mm AM/PM => 08:35 AM
  • [h]:mm => 27:13 (to display over 24 hours)
  • But you can't write mm alone (because Excel understands that it's for Months)
Examples of custom code for Time format

Where to write this Code?

  1. Go to Dropdown list of the number format
  2. Click on the last option More Number Formats
Custom Number Format
Custom Number Format
  1. In the dialog box, select the Custom Category.
  2. In the text box, write your code
  3. Click on OK
Where to write the custom time format code

Leave a Reply

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