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.The diagram 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."
- 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
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.
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.
The diagram 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." |
No comments:
Post a Comment