Formatting Cells is a way of changing the view of the Cell. All Cells by default is set as general with no specific format being assigned. Although as you would notice Excel tries and figures out the best format like Dates.
To Get to Number Formatting you can right-click on the specified Cell, Row or column and Choose Format Cells… alternatively press CTRL + 1
Date and Time Serialization
Date and Time are the fourth and fifth explicit options you can choose, both of them are also the perfect example of how Excel only changes the view of the cell and not the actual data.
They are actually stored in something call Serialization. This is basically a calculated floating-point number. So what they have done is created a Fictional Day Zero. This is is Midnight 1900\01\00 meaning that the number one (1) is 1 January 1900.
Note that the older version of Excel or similar apps did use a different date ( January 1, 1904 ) but it is compatible in all systems.
All decimal number then represents the Time for that specific day where 0.5 then represents 12:00:00 PM and the full number 1.0 then represents 12:00:00 AM.
This Also means that Excel can not calculate dates prior to 1900/01/01. See below Examples:
Now the problem with this is that a normal day only has 86400 seconds, what happens when you enter the Value 0.99999.
| Day | Hours (24) | Minutes (60) | Seconds (60) |
| 1 | 24 | 1440 | 86400 |
Although your excel options when it comes to selecting pre-created formats are limited, excel has the option to go into fractions of seconds when using Custom Formats. To understand Custom Formats we first need to work through all the other Format Types.
As per the below Spreadsheet, we can then see that different decimal values have the same number in seconds but when going into a fraction of seconds they are actually different.
Formats
The Formats in the Category is straight forward and what you choose is how it will be displayed, for the exception of the options marked with an asterisk (*). These formats are based on the preferences in the control panel under the Regional Settings. Which specifies the user’s preferences on how too display either Short\Long Date\Time.
