How to Calculate Age in Months & Days Using the Mod Function
- 1). Type a person's birthday into cell A1 of a Microsoft Excel spreadsheet. For example, type 10/15/1982.
- 2). Type CTRL-; in cell A2 to display today's date. For example, 1/1/2000.
- 3). Type "=A2-A1" in cell A3. This will display the difference between the two dates in terms of days. Using the above numbers the result should be 6723.
- 4). Type "=int(A3/30.4375)" in cell A4. This formula divides the days from the previous step by 30.4375, the average number of days in a month (accounting for leap years). The "int" function returns an answer without a remainder which in this case is 220 the number of whole months since the person's birth.
- 5). Type "=mod(A3,30.4375)" in cell A5. This formula divides the days by 30.4375 again but this time it returns the remainder. In this case the result is 26.75, the number of days since the end of the previous whole month. If desired type instead "=int(mod(A3,30.4375))" for the result in whole days.
Source...