Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want.
Date and Time Formats
Dates and times in Excel can be displayed in a variety of ways. To apply a Date or Time format, execute the following steps.
1. Select cell A1.
2. Right click, and then click Format Cells.
3. In the Category list, select Date, and select a Date format.
4. Click OK.
Note: to apply a Time format, in the Category list, select Time.
5. Dates are stored as numbers in Excel and count the number of days since January 0, 1900. Times are handled internally as numbers between 0 and 1. To clearly see this, change the number format of cell A1, B1 and C1 to General.
Note: apparently, 42544 days after January 0, 1900 is the same as June 23, 2016. 6:00 is represented as 0.25 (quarter through the day).
6. You can enter times as 6:00, but Excel displays this time as 6:00:00 AM in the formula bar. AM is used for times in the night and morning. PM is used for times in the afternoon and evening.
7. Change the number format of cell C1 to Date only.
Note: cell C1 still contains the number 42544.25. We only changed the appearance of the number, not the number itself.
8. Finally, if you cannot find the right date or time format, create a custom date or time format.
Custom Time Formatting Characters
Like dates, time also has its own set of custom formatting characters, as listed below:
| Character | Explanation | ||
|---|---|---|---|
| h | Displays the hour as a number without a leading zero. | ||
| [h] | Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss or [h]:mm | ||
| hh | Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. | ||
| m | Displays the minute as a number without a leading zero.* | ||
| [m] | Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. | ||
| mm | Displays the minute as a number with a leading zero when appropriate.* | ||
| s | Displays the second as a number without a leading zero. | ||
| [s] | Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. | ||
| ss | Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. | ||
| AM/PM, am/pm, A/P, a/p | Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight. |
Custom Time Formatting Examples
