Presentation of the date’s format
Rule 1: Dates are numbers.
To 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.
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.
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)
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.
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)
But 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.
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.