«

»

Date format

It's important to understand how Date and Time are managed in Excel to custom their format.

What is a date in Excel?

Display number and not a Date. It's normal😉

In Excel, it is possible that a cell doesn't display a date as you expect but a number such as 42370.

For a lot of users, it's a bug of Excel but not at all!

Dates are whole numbers

Here is a date in a cell

Now, by changing the format to Standard, the cell displays 42370.

The 01/01/2016 and 42370 are the same value but with different formats

In fact, a date is the gap in number of days between the 01/01/1900 (which is the starting point of the dates in Excel) and your date. So 42370 is the number of days between 01/01/2016 and 01/01/1900.

Don't write text in your date ⚠

With dates, one of the most common mistakes is to write text inside the date (1 January 2016 for example)

Never do this in Excel ⛔⛔⛔

If you do this, the contain of the cell is a Text and not a number

  • In Excel, text is always on the left of a cell
  • A number or a date is on the right

The good practice

In fact, with Excel, you can display the same date in a different way (even with text like January) but it still a number.

  • 01/01/2016
  • 01 January 2016
  • Friday 01/01/2016
  • Fri 01 January 2016
  • ...

Date format

When you have a date in a cell, you can display it in different ways using the following options.

In the ribbon, you have 2 options

  • short date
  • long date

 

 

 

 

 

How to customize your date format?

A date has 3 parameters

  • d for the day
  • m for the month
  • y for the year

In function of your local setting, the letter could be different (t for tag in Germany or j for jour in French, ...)

To customize a date, open the dialog box Custom number with one of this following method

  • Either by clicking on the tiny slanting arrow just below each section of the ribbon.
  • By pressing the keyboard shortcut Ctrl + 1
  • Or by clicking on the menu More number formats (at the bottom of the list of number formats)

In this dialog box, you select in the right-hand side 'Custom' and in 'Type' you write the code of your date.

Now, you just have to write the parameter d, m or y different number of times. By entering the codes 1,2,3 or 4 times, the display will be different.

All the display in function of the different code

The following document shows you the result on the date in function of the code

Different examples of custom date

Here, you have different examples of display with the same date.

 

Difference between Date and Time in Excel

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

This rule is fundamental to avoid mistake in the calculation and display. A whole number will never be understand as an hour or minute.

 

 

For example,

  • Insert the value 8 in a cell
  • Apply the format' Time'
  • Excel displays 00:00:00 in your cell

That is not an error.

For Excel, 8 is 8 days. So it's normal that if you convert in Time, the time is 00:00:00 for midnight

You can see that if the format of the cell is Date & Time, you have the value 8 for the date 08/01/1900

Convert a whole number is Time

So, if you have to convert an whole number in Time, you must divide that number by 24 (the number of hours in a day 😉).

=8/24

So the result is 0,3333 but if you apply the Time format, you have 08:00:00 in the cell.

There is a very easy way to convert a whole set of whole number in fraction with paste special (option divide).

Time format

Customize Hours, Minute and second

You can customize the display of your time exactly like for the date. The parameters are

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

It is not possible to have the minute alone. Because m is by default the parameter for the month. So you can have, hour & minute, minute & second but never minute alone.

Hour greater than 24H

If you want to display hour after 24H, you have to write the code between bracket

=[h]

All the Time format in one document

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/date-format/


Leave a Reply

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