Explanation
Dates and times in Excel are stored as serial numbers and converted to human-readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. Similarly, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000, is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:
=TEXT(A1,"mmm") // "Jan"
=TEXT(A1,"dd/mm/yyyy") // "09/01/2012"
=TEXT(A1,"dd-mmm-yy") // "09-Jan-12"
Date format codes
Assuming a date of January 9, 2012, here is a more complete set of formatting codes for a date, along with sample output.
| Format code | Output |
|---|---|
| d | 9 |
| dd | 09 |
| ddd | Mon |
| dddd | Monday |
| m | 1 |
| mm | 01 |
| mmm | Jan |
| mmmm | January |
| mmmmm | J |
| yy | 12 |
| yyyy | 2012 |
| mm/dd/yyyy | 01/09/2012 |
| m/d/y | 1/9/12 |
| ddd, mmm d | Mon, Jan 9 |
| mm/dd/yyyy h:mm AM/PM | 01/09/2012 5:15 PM |
| dd/mm/yyyy hh:mm:ss | 09/01/2012 17:15:00 |
You can use the TEXT function to convert dates or any numeric value to a fixed text format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left. Also, see Excel custom number formats .
Explanation
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:
| Hours | Fraction | Value | Time |
|---|---|---|---|
| 1 | 1/24 | 0.04167 | 1:00 |
| 3 | 3/24 | 0.125 | 3:00 |
| 6 | 6/24 | 0.25 | 6:00 |
| 4 | 4/24 | 0.167 | 4:00 |
| 8 | 8/24 | 0.333 | 8:00 |
| 12 | 12/24 | 0.5 | 12:00 |
| 18 | 18/24 | 0.75 | 18:00 |
| 21 | 21/24 | 0.875 | 21:00 |
This means if you have a decimal number for hours, you can simply divide by 24 to get the correct representation of hours in Excel. After dividing by 24, you can apply a time format of your choice, or use the result in a math operation with other dates or times.
In the example, since B10 contains 12 (representing 12 hours) the result is 12/24 = 0.5, since there are 12 hours in a half of day. Once a time format like h:mm has been applied, Excel will display 12:00.
Durations longer than 24 hours
To display hours that represent a duration longer than 24 hours, you’ll need to adjust the number format. Just wrap the h in square brackets like so:
[h]:mm
To display in minutes, you can do the same thing with m:
[m]
The brackets tell Excel the time is a duration, and not a time of day. This article explains number formats in more detail.
