Excel Formula : Calculating Age

Today I am sharing with a long formula for calculating age based in Date of Birth as input.

=YEAR(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Months ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Days")

This formula calculates age of a person with the accuracy of exact years , months and days

This considers that the Date of Birth is available at Cell A1 of the worksheet. You will need to replace this

Download excel file containing formula to calculate age based on Date of Birth

Excel Sort Dates by Birthday

Today's post is in response to comment by Donna.

Problem on hand is to sort data based on birthday of a person. If you sort it on the date of birth it has year also and you will not be able to sort them by month and date.

One of the possible solution is to drop the Year and then sort them. You will need to add one more column to your data as sort key and use following formula considering that you have Date of Birth in Cell "B2"

=TEXT(B2,"MMDD") will convert 02-Dec-50 as 1202 and 24-Sep-89 as 0924. Now if you sort your data based on new column "Sort Key" on ascending manner, you will get 24-Sep-89 before 02-Dec-50

You will be able to get this result only if the date of birth in your data is a real date. In case not you will need to convert text date to real dates

Use following formula to know Birthday during current year

=--TEXT(B2,"DD/MMM")

You will get error in case birthday is 29-Feb and current year is not a leap year. I suppose this is correct as person will not have birthday every year.

There are couple of other solutions to the sort dates on birthday , you can share one by way of comments if you know

Excel Dates : Leap Year or Not a Leap Year

Excel follows Gregorian calendar which was first established in 1582 by Pope Gregory XIII.

To determine whether a year is a leap year, follow these steps:

  1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5. 
  2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4. 
  3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5. 
  4. The year is a leap year (it has 366 days). 
  5. The year is not a leap year (it has 365 days). 

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")

Since excel has all these calculations built in , you can just test the last day of the February month and decide based on day value. If it is 29 then Leap Year else NOT a Leap Year.

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(DAY(DATE(A1,3,0))=29,"Leap Year","NOT a Leap Year")

First Formula returns 1900 as "NOT a Leap Year" but second Formula will return it as "Leap Year". This is due to bug in excel which considers 1900 as a Leap Year though it was not a leap year.

This is why I will recommend to use second Formula in excel to determine a Leap Year since it takes care of any bug that exists in excel

VBA Function for Leap Year Test:

Public Function IsLeapYear(ByVal YY As Long) As Boolean
    IsLeapYear = Day(DateSerial(YY, 3, 0)) = 29
End Function

Leap Year , leap year test , leap year check , check leap year , Leap Year in Excel

Excel Convert Text to Date

Many of us get into a situation when the dates in our data are coded as text. Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula.

You may have a situation where the text of the date is actually as date but being a text string it is not recognised as date by excel. It could be 12/Oct/2009 in text string. You can easily convert them using Datevalue formula. Assuming you have text in cell A2, the formula you need to enter is =DATEVALUE(A2)

However many time we get data stored like 20091012. Here October 12, 2009 has been stored as First 4 digits as Year, next 2 Digits as Month, next 2 Digits as Day. It becomes little tricky but it can also be converted to date using combination of Mid and Date function of excel.

Assuming you have date stored as 20091012 in cell A3 of you worksheet, you can convert it to date using =DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

Logic behind this formula is devide the text into Year , Month and Day separatly. This is done by using MID function of excel. Once you are able to break text string into three different components of Date. You put these in the Date Function of Excel. Syntex for Excel Date function is DATE(year, month, day).

MID(A3,1,4) = 2009 - This Year Value in Text String
MID(A3,5,2) = 10 - This is Month Value in Text String
MID(A3,7,2) = 12 - This is Day Value in Text String

This is converted into Grand Formula = DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

If you see the formula I have just brokenup the text string using MID in combination with Date function.

See picture below to understand it better.

convert text to date in excel , excel convert text to date, convert text to date, date values

Custom Formating - Excel Date Format

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

You can view them as you want based on how do you format the cells. There are predefined date formats available when you choose format cells option. However you can use custom format option to display dates if available formats does not serve your requirement.



You can choose following codes or combination of it to view date in excel as you want. I have used highlighted codes dd mm yyyy with "/" to show today's date as 16/02/2009 in above screen. You can use any of these code singly or jointly with any character in between.

Entering last day of the month in Excel

Entering a specific date in excel is easy as you know what to enter, however if you are asked to enter last date of the month it becomes little tricky as last date of the month varies between 28 to 31 depending on what year and month it is.

However Excel date mathematics makes it easy. What you need to do is enter the first day of next month and then reduce it by 1, you will get last day of previous month.

Look at following picture to understand it better.


I have used date function to enter Feb 1, 2009 and then reduced the day by 1, other wise you can enter =Date(2009,2,0) also to get same result. after you have added above formula in Cell B4 you can use this formula to enter last day of next months in cell B5 =DATE(YEAR(B4),MONTH(B4)+2,0). You can further copy this down to get next month.

Excel date mathematics

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. Will tell you date mathematics formula.

How to Add / Subtract month from a date in Excel.
Adding / Subtracting a month from a particular date in excel looks tough as each month has different number of days. However it is very simple if you use following formula.
=DATE(YEAR(C4),MONTH(C4)+1,DAY(C4))

In this base date is available at cell C4 and we have added 1 to month value to increase it by one month. Similarly you can subtract required number of months from base date. See picture below for further understanding of it.


Addition of more than 12 months will result into addition of year. See picture below:-



Addition of 15 months have resulted into increase of one year and 3 months.

This will work for Year addition / subtraction also.

Date Add , Date Function , excel date mathematics , Excel Date math , using date , excel date calculate , excel date calculation , excel date function , excel date functions

How Microsoft Excel stores dates and times

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

A day equals 1, half a day is .5 and an hour time interval is therefore 1/24=0.041666667

Dates entered without a specified year are assumed to be in the current year

Dates entered with a 2 digit year are stored as pre-2000 dates if the year is between 30 and 99 or as post-2000 dates if the year is between 0 and 29.

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900 and Microsoft Excel for the Macintosh uses 1904 date system.

Two different date systems got created as 1900 is not a leap year and design of early Macintosh computers did not support dates before Jan 1, 1904. 1900, although divisible by 4, wasn’t a leap year. By using 1904 as a base, they saved a few bytes code, and every byte counted when they tried to squeeze the entire MacOS into 64KiB of ROM…

The following table shows the first date and the last date for each date system and the serial value associated with each date.



To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box. This setting is workbook specific, you can have two workbooks on the same system with 1900 and 1904 date system.

Be careful while changing the date system. The workbooks with actual dates recorded will also shift by 1462 days.

Similarly you may get into trouble when linking two workbooks set with two different date systems. in case you get struck with such situation you can correct this by adding or subtracting 1462 from one of the workbook as the case may be.

Suppose Book1 works on 1900 date system and book2 works on 1904 date system. If you are linking dates from 1904 to 1900 date system workbook, you will add 1462 to all the dates linked from 1904 date system. In a reverse scenario you will subtract 1462.

Dates in Excel , Excel Dates, Excel Date