| | Date and Time functions | Detailed Site Index | Display numbers as words | | ||
It is easy to determine the number of days between any two dates simply by subtracting one from the other, but it is extremely difficult to return a string that says something like '24 years, 6 months and 8 days'. The maths and logic involved in this apparently straightforward question are very complex. You may wish to try it using the MONTH() and DAY() functions, but make sure you thoroughly test your formula!
An Excel function called DATEDIF() will perform this calculation although it is not included within the Excel help files or the function wizard until Excel 2000. It does however exist in previous versions of Excel. It takes the following structure: =DATEDIF(Date1, Date2, OutputRequirement). The first two parameters are simple, although you must ensure that the second date is greater than the first. The OutputRequirement parameter is a character code (typed inside inverted commas) that specifies the type of value you wish to obtain.
| Code | Description | Explanation |
| "d" | Days | The number of days between Date1 and Date2. |
| "m" | Months | The number of complete months between Date1 and Date2. |
| "y" | Years | The number of complete years between Date1 and Date2. |
| "yd" | Days excluding Years | The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year. |
| "ym" | Months excluding Years | The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year. |
| "md" | Days excluding Months and Years | The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year. |
This function is ideally suited to calculating someones precise age on any particular date or the number of days until your next birthday. The first date can be your date of birth and the second date is either entered in another cell by the user, or automatically set to todays date with the NOW() function.
| - | A | B | C |
| 23 | First Date | Second Date | |
| 24 | Date as typed | 02/02/84 | 06/06/07 |
| 25 | Date formatted | 02 Feb 84 | 06 Jun 07 |
| 26 | DateDif using 'D' argument | 8525 | |
| 27 | DateDif using 'YD' argument | 125 | |
| 28 | DateDif using 'MD' argument | 4 |
| [C26] =DATEDIF(B24, C24, "D") |
| [C27] =DATEDIF(B24, C24, "YD") |
| [C28] =DATEDIF(B24, C24, "MD") |
The diagram (right) illustrates the difference between the different types of day count. The first formula (row 26) counts the total number of days between the 2 dates. The next formula counts the days which are the remainder after the whole years are deducted. The final formula returns the number days after both the whole years and the months have been deducted.
| - | A | B | C | D |
| 30 | First Date | Second Date | ||
| 31 | Date as typed | 02/05/97 | 06/06/07 | |
| 32 | Date formatted | 02 May 97 | 06 Jun 07 | |
| 33 | Years | 10 | [B33] =DATEDIF(B31, C31, "Y") | |
| 34 | Months | 1 | [B34] =DATEDIF(B31, C31, "YM") | |
| 35 | Days | 4 | [B35] =DATEDIF(B31, C31, "MD") | |
| - | A |
| 38 | Your age is 10 years, 1 months and 4 days. |
| 39 | [A38] ="Your age is " & TEXT(B33, "0") & " years, " & TEXT(B34, "0") & " months and " & TEXT(B35, "0") & " days." |
The second diagram (left) shows how the function can be incorporated within a complex formula to return a sentence describing a time interval (in cell A38). The TEXT() function is the correct way of incorporating both numeric values within a string of characters.
General information about Date and Time functions is also available within this site.
It is possible to convert whole phrases, including number values, into proper words (e.g. 'Twelve years and Four days'). This requires a user defined function such as is shown here.
 |
Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: xl_birthday.htm | © meadinkent.co.uk 2016 | Last updated Feb14 | CMIDX S4 P3 Y |