↑ Return to Date & Time

Date format

In Excel, the format of the date is very important to avoid a lot of mistakes in your formulas. Let’s see how to work with dates.

 

Presentation of the date’s format

Rule 1: Dates are numbers.

Date_Format_1To check this, write any date in a cell and change the default format from Date format to the General format. In this case, you don’t see your date but only a number. This figure represents the difference in days between the 01/01/1900 (which is the starting point for dates in Excel) and your date.

It is also possible to start at 01/01/1904 but this will be reviewed for formatting hours the bottom of this page.

 

 

 

 

 

 

 

Date_Format_2
It is important to check if the content of a cell is numeric or a string. In Excel, it’s easy to identify a number because it’s positioned by default on the right of a cell (unlike text that default position on the left).

In this picture, if you have a date that is left justified, it is not a date in the sense of Excel as shown in this example.

 

 

 

 

Date_Format_3If you want to see exactly 01 January 2014 in a cell and keep the numeric value, you just have to insert a date in a cell and change its format.

You can see in this example that the content is still located on the right of the cell. So it’s a number, you can make calculation with it but the way to display the date is a text 🙂

 

 

 

 

Rule 2: The value 1 is always equal to one day (and never 1 hour)

Date_Format_5This rule is very important to understand how to make calculations about the date and also hours.

For example, if you have the value 8 in a cell and apply the format ‘time’, your cell displays 0:00:00.

This is not an error. Excel displays it the 8th day from 01/01/1900.

 

 

 


Rule 3: Time is a fraction of a day.

Date_Format_6If you want to display in a cell the value 08:00 hours, you must divide that number by the number of hours in a day (24) and then apply the Time format.

If you change the Time format to General format, the result is 0.333333 which is logic because 8 hours is one third of a day.

Now if you want to find the number of minutes, you must divide by 24 first (number of hours in a day) and also divide by 60 (number of minutes in one hour)

 

 

FORMAT DATES

Date_Format_9When you have a date in a cell, you can display it with different Number format. Since the ribbon, you short date and long option.

 

 

 

 

 

 

 

 

Date_Format_7But you can customize each component of a date (day / month / year) using the customization dialog box numbers. To display the dialog customization numbers, you click the arrow just below the ribbon or by activating the keyboard shortcut Ctrl + 1.

 

 

In this dialog box, you select the right part ‘Custom’ and‘Type’ you will even set how to display your date.

Date_Format_8

Each component of a date can be changed at your convenience:

  • d for days
  • m for month
  • has for years

In the ‘Type’, simply to indicate how you want the date to be displayed based on the example in the following table.

But you can also combine all these types to display dates in different ways and in all cases these dates are numbers because they are right justified.

 

FORMAT OF HOURS

Similarly as dates, it is possible to change the format of hours (h), minute (m) and seconds (s). But there are special.

  • To display hours beyond 24, you must write the code in brackets hours [h]. In the example below, Excel shows the number of hours since 01/01/1900
  • It is not possible to write without hours or minutes without seconds because the format is interpreted as the month
  • For tenths cents and beyond, just add 0 to your format to display this information in your cell

The following table outlines differences with the corresponding code.

Leave a Reply

%d bloggers like this: