In Excel time is treated as a fraction of a 24 hour day from the number 0 to 1, with 0 being 12:00am (Midnight) and 1 representing 12:00am of the next day.
All time is treated as this underlying number, but the formatting can be changed for presentation purposes in the spreadsheet, so while the underlying serial number 0.75 represents 6:00pm (three quarters of the way through the day) you could format this as 18:00 if you wanted to use a 24 hour clock system in your spreadsheet without affecting the underlying serial number.
Download File
If you would like to follow along download the attachment below.
Decimal Values to Represent Time in Excel
Excel represents time as a decimal value, where one full day equals 1. The decimal value represents the amount of the day that has passed.
For smaller units of time smaller decimal values can be used.
These values can then be multiplied by the desired number of seconds, minutes or hours that you want to get the underlying serial number.
When combined together you can create decimal values for specific times of the day.
Why does Excel use serial numbers for time
By using serial numbers for time Excel can more easily perform calculations as it will use the serial number as the basis for calculations.
This means that whatever format is applied to the time it will always have a fixed serial number to work with.
Quickly Formatting Time Values
Most of the time, but not always, if you enter a time in Excel it will show as a time value rather than the underlying decimal value.
If you do see the decimal value and want to quickly change this to a time value with a 24 hour format you can use the format drop down on the Home tab.
To do this click on the cell you want to change the formatting of, go to the Home tab, and in the Number section click on the Number Format drop down.
This will drop down a list of preset number formats. Select Time to change a decimal value to a 24 hour time format.
You can also use this formatting method to change a time value to a decimal value by changing the format of a cell with a time value in it to General or Number.
More Advanced Time Formatting
If you want your time value to show in a format other than 24 hours then you can choose from more options by selecting ‘More Number Formats…’ on the dropdown or clicking on the cell with the time value and pressing Ctrl + 1.
Either option will open the Format Cells window, where you can select Time from the Category box on the left hand side which will allow you to change the time format from a list of pre set options.
Manually Format Time Values
For more flexibility in how your time values are formatted, in the Format Cells window you can go to Custom in the Category box on the left hand side to manually enter a time format.
The manual formats are made up of a combination of codes representing the time formats.
H or HH: Represents hours. Use a single H for 1-digit hours (e.g. 6), and HH for 2-digit hours (e.g. 06).
M or MM: Represents minutes. Use a single M for 1-digit minutes (e.g. 5), and MM for 2-digit minutes (e.g. 05).
Note: M or MM is interpreted as "month" if used outside a time context, so make sure to use it within a time-based format.
S or SS: Represents seconds. Use a single S for 1-digit seconds (e.g. 7), and SS for 2-digit seconds (e.g. 07).
If only using the letter codes the format will default to 24 hours, but by entering AM or PM at the end of the format it will show in a 12 hour format followed by AM or PM depending on the time of day.
Below are examples of how different combinations would show the time 18:05:07 (Five minutes past six pm and seven seconds).
Conclusion
Excel treats all times as a decimal value between 0 and 1, with the decimal number representing the times point in the day based on hours, minutes and seconds, so three quarters of a day, 6pm, would show as 0.75.
This allows Excel to make calculations from time values based on the underlying decimal number regardless of how the time is formatted.
The format of a time value can be changed from either the preset formats on the Number Format dropdown on the Home tab, from the Time section of the Format Cells window, or manually from the Custom section of the Format Cells window.
Comments