This figure represents the number of days from 01/01/1900 (which is the starting point dates in Excel). It is also possible to start at 01/01/1904, but this will be studied for the formatting of hours at the bottom of this page.
On the opposite, if you have a date that is left justified, it is not a date within the meaning of Excel as shown in this example.
If you want to see exactly Saturday, January 1, 2011 in a cell, you write it as a date but in a particular format (see date’s format below). As this data is right justified in the cell, it is indeed a figure even if it looks like a text.
For example, if you have the value 8 in a cell and you apply the format ‘Time’, your cell displays 00:00:00.
This is not an error. Excel displays the eighth day from 01/01/1900.
When you have a date in a cell, you can display it in different ways due to formatting options. In the ribbon, you have the option short date and long date.
But you can customize each component of a date (day / month / year) using the customization dialog box numbers. To display the dialog box to customize numbers, you click the arrow just below the ribbon or by activating the keyboard shortcut Ctrl + 1. In this dialog box, you select in the right part the option ‘Custom’ and you fill your format in the ‘Type’ zone.
Each component of a date can be changed:
- d for days
- m for the months
- has for years
In the ‘Type’,simply indicate how you want to display the date based on the example of the table.
But you can also combine all these types to display dates in different ways and in all cases these dates are numbers as they are right justified.
Format of Hours
In the same way as for dates, it is also possible to change the format of hours (h), minutes (m) and seconds (s). But there are special.
- To view hours beyond 24, you must write code for hours in brackets [h]. In the example below, Excel shows the number of hours since the 01/01/1900
- It is not possible to write the minutes without the time or without the second because otherwise the format is interpreted as 1 month
- For tenths, cents and beyond, simply add 0 to format to display your information in your cell
The following table outlines differences with the corresponding code.