How to convert birthdate to age quickly in Excel?

For instance, you get a range of various birthdate data in Excel, and you need to convert these birthdate to display their exact age value in Excel, how would you like to figure out? This article lists some tips to convert the birth date to age in Excel easily.

Convert birthdate to age with formulas

Easily convert birthdate to age without remembering formulas


arrow blue right bubble Convert birthdate to age with formulas


Method A: Convert birthdate to age with subtraction

Normally we figure out someone's age with current date subtracting the given birth date. So does it in Excel.

Step 1: Enter current date in a blank cell, such as 2016/4/7 in Cell B2.

Step 2: In another blank cell (says cell C2), enter the formula =INT((B2-A2)/365), and press the Enter key.

Method B: Convert birthdate to age with DATEDIF function

Some would like to apply the DATEDIF function to calculate the age. Enter the formula =DATEDIF(A2,NOW(),"y") in a blank cell, it calculates the age immediately after pressing the Enter key.

Method C: Convert birthdate to age with ROUNDDOWN function

Another function to convert the birth date to age is =ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0), which will figure out a standard age, such as 27.

Method D: Convert birthdate to exact age with DATEDIF function

Sometimes exact age is requires, and you may want to know how many years, months, and days from the birth date to current date. The following formula can help you figure out:

=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"

See the screen shot above, this formula will show you precise result, such as 23 Years, 8 Months, and 14 Days.

Formula is too complicated to remember? The Auto Text feature of Kutools for Excel can save the formula as an Auto Text entry for reusing with only one click in future!
Read More...   or   Download the free trail now!

arrow blue right bubble Easily convert birthdate to age without remembering formulas

You can easily convert birthdate to age without remembering formulas with the Calculate age based on birthday function of Kutools for Excel.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select a blank cell for locating the age. Then click Kutools > Formula Helper > Calculate age based on birthday. See screenshot:

2. In the Formula Helper dialog box, select the cell with the birth date you need to calculate in Date box, and then click the OK button. See screenshot:

3. After clicking OK in the Formula Helper dialog box, the age based on the birth date is populated in selected blank cell immediately. You can drag the Fill Handle to the cells you need to convert the birthdate to age. See screenshot:

If this utility saves your time, you can download and try the free trial now!

You may interest in:

Combine multiple worksheets/workbooks into one worksheet / workbook:

Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now


arrow blue right bubbleRelated Articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +6 Berjang Pun
:lol: it is helpful for me
2013-11-29 16:38 Reply Reply with quote Quote
Permalink -1 AMIT
[quote name="Berjang Pun"]:lol: it is helpful for me[/quot
VERY EASY TO KNOW THE DATE OF BIRT COUNT AS YEARLY AGE..?
YOU CAN ..AS BELOW
10/8/1980-TODAY DATE=?????? THEN YOU CAN / BY 365 EASILY U WILL GETS THE POINT..
2014-09-01 12:25 Reply Reply with quote Quote
Permalink 0 Robyn
I've been looking for the "round down" option, but didn't know there was one. Thank you!
2013-12-13 20:51 Reply Reply with quote Quote
Permalink +3 Thom H
=FLOOR() would also do the trick
2014-03-12 23:48 Reply Reply with quote Quote
Permalink 0 she
Quoting Thom H:
=FLOOR() would also do the trick

how is this being used? () what should be inside this parentheses?
2014-08-07 05:46 Reply Reply with quote Quote
Permalink +1 Thom H
Hi Robyn,

The Cell you wish to round down should be inside of the parentheses i.e. if you are rounding down cell A1 it would be =Floor(A1)
2014-11-17 12:44 Reply Reply with quote Quote
Permalink +3 S Dorn
I need a formula that will allow me to punch in a DOB and then find out the exact day that a person turns exactly 70 1/2.
2014-01-28 17:06 Reply Reply with quote Quote
Permalink 0 Thom H
=EDATE(A1,906) with DOB in Cell A1
2014-03-12 23:47 Reply Reply with quote Quote
Permalink +3 Amit sharma
This is very helpful and very clear to understand for any one. I also need it. It help me a lot.
2014-02-01 19:03 Reply Reply with quote Quote
Permalink +1 MICH
very nice..A13 means A2..Very helpful..makes work easier..thanks for the formula..
2014-02-08 11:37 Reply Reply with quote Quote
Permalink -2 Roger
Try this formula to convert date of birht to Age.
2014-02-10 13:27 Reply Reply with quote Quote
Permalink -1 kaushalendra
it is very helpful. It is helpful to beginners as well as skilled person
2014-02-17 10:43 Reply Reply with quote Quote
Permalink +3 SDJohnson
I have been looking for a formula that gives me the age from a Bdate for months now, I found several but they never worked. This was so easy to understand..... . Made me look for other items. :-) Thank you so much.
2014-02-26 23:33 Reply Reply with quote Quote
Permalink -1 fawadkhan
hello this is fawad khan want some information about ageses
2014-03-01 11:26 Reply Reply with quote Quote
Permalink +3 jayson
i have a whole bunch of cells i have to convert to age, how do i do it faster?
2014-03-19 05:19 Reply Reply with quote Quote
Permalink 0 Thom H
create the chosen formula in the cell to the right of the first DOB, then you can drag the function down all of the rows with the anchor in the bottom right of the cell containing the function.

This method will work for most of the above, but if you're using method a. i.e. One where the current date is a reference to a different cell that contains the current date, you'll need to amend the top formula to make it into an absolute reference, so if your formula was option one (=(B2-A2)/365) assuming your dates are all in column A, you can amend the too function to =($B$2-A2)/365. Notice how I've anchored the b2 cell reference while the a2 cell reference will change as you drag the function down.
2014-03-20 08:37 Reply Reply with quote Quote
Permalink -4 KASSEM
hi i tried all of those methods and it didnt work out it alwyas return with result (#value!)
2014-05-01 12:32 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Kassem,

#Value means that the Cells your Function refers to are not in the correct format for the Formula you are using i.e. using =SUM() on two Cells containing Text. All of the above functions are written assuming we're working with Cells containing only Dates.

The trouble is, Excel can sometimes see something as a different format to that which you see. Firstly, make sure the dates are in the correct uniform (DD/MM/YYYY). Secondly, make a small amendment to the Cell references in the Date Formula using the =text() function to tell it to read it as a Date .

Assuming we're using the DATEDIF() function with the Birthdate in A1 and the current date in B1, which would be =DATEDIF(A1,B1, "y"), we can force the Function to read Cell A1 as a date by amending it like so =DATEDIF(TEXT(A 1,"DD/MM/YYYY") ,B1,"y").
2014-05-03 13:39 Reply Reply with quote Quote
Permalink 0 Daini Malhotra
i like method d for calculating age, it's very easy & suitable formula in excel.
:D
2014-05-04 06:38 Reply Reply with quote Quote
Permalink +1 Daini Malhotra
=DATEDIF(A13,TO DAY(),"Y") & " Years, " & DATEDIF(A13,TOD AY(),"YM") & " Months, " & DATEDIF(A13,TOD AY(),"MD") & " Days" this formula is very easy & suitable for calculating age in excel, i like it............. .
2014-05-04 06:40 Reply Reply with quote Quote
Permalink -2 Rasel
a problem i faced. like: birth date = 1st Nov.'2014, today is 31st Dec.'2015 then the result comes = 1 year, 1 month, 30 days, but the days should be 1day more i.e. 31 days. how can I do this in the above formula? pls help
2016-01-24 05:47 Reply Reply with quote Quote
Permalink 0 Mubeen
yes its very helpful for me. i easily understand. :)
2017-01-19 07:46 Reply Reply with quote Quote
Permalink -2 mark04
method D is returning a value while date cell is blank, how to return value "0" or blank when there's no date filled in cell?
2014-05-07 14:33 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Mark

Excel uses an internal calendar which starts at 01/01/1900. When a start date isn't present it will automatically choose the first date in its calendari I'd say use an IF() function, assuming the start date is in cell A1, you could use =IF(A1="","",IN SERT CHOSEN DATE FUNCTION HERE)

This function checks cell A1, then returns no value if the cell is blank, but does your chosen formula if the cell is active (contains data).

If it's likely that sometimes the start date will be blank and sometimes the end date will, then you'll need to add in the AND( ) function so once again assuming start date is in Cell A1 and end is in B1 you'd use =IF(AND(A1>0,B1 >0),INSERT CHOSEN DATE FUNCTION HERE,"")
2014-05-08 06:45 Reply Reply with quote Quote
Permalink 0 JENNIEJEN
THOM H...
thank you so much... you're the best!
2017-02-14 15:39 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Mark04,

Excel will automatically treat the Empty Cell as a date and just use the first date in it's Calendar (01/01/1900). You'll need to use an IF() function.

Assuming the start date is in A1 and the end date is in B1 I'd go for =IF(AND(A1>0,B1>0),INSERT CHOSEN DATE FUNCTION HERE,"")
This will leave the Cell Blank if there isn't a value in both Cells A1 & B1.
2014-05-08 21:22 Reply Reply with quote Quote
Permalink -2 Meak Kanann
I really love this website. I can find whatever I need immediately. Plz continue to update n search for more useful function. :P
2014-05-09 06:46 Reply Reply with quote Quote
Permalink -2 mallikarjuna swamy
04/05/2012
=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TOD AY(),"YM") & " Months, " & DATEDIF(A13,TOD AY(),"MD") & " Days"



i WANT TO CALCULATE DATE AS OF TODAY BUT WHEN I INSERTED THE FORMULA IN THE CELL I AM GETTING

114 Years, 4 Months, 10 Days WHICH IS OBVIOUSLY WRONG
2014-05-10 02:23 Reply Reply with quote Quote
Permalink 0 Thom H
Is the first date definitely in Cell A13 as that's where the Argument is looking for the first date. It looks as though A13 is blank, thus it's counting the difference between today and the start of Excel's Calendar (What it would consider as date 0).

The only other thing it could be is that it's reading the first date as blank because it's not in the correct format, but I believe that would bring up a #Value error. Either way, it's worth checking the format of the Cell. You can either go right click> format Cells> Date> OK, or you can switch each reference to Cell A13 with an =TEXT() so it would be:
=DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"Y") & " Years, " & DATEDIF(TEXT(A1 3,"DD/MM/YYYY") ,TODAY(),"YM") & " Months, " & DATEDIF(TEXT(A1 3,"DD/MM/YYYY") ,TODAY(),"MD") & " Days"

Before all of that though, did you copy and paste your Function straight from your Spread sheet, because I error checked it and there were a couple of spaces in the TODAY() functions that threw it and gave me a #NAME error. Try it again by copying and pasting the following:

=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TOD AY(),"YM") & " Months, " & DATEDIF(A13,TOD AY(),"MD") & " Days"

Once again, make sure your start date is in Cell A13!
2014-06-04 19:05 Reply Reply with quote Quote
Permalink -2 guru
Hi,,,

you only looking this only ya,,, :-)
2015-07-08 07:49 Reply Reply with quote Quote
Permalink 0 Dennison
Does Method D compensate for the day lost/gained on a leap year?
2014-05-13 13:42 Reply Reply with quote Quote
Permalink -2 Jodi
Hi, How do you use Index and Match function to find age from a person birth date? Thanks.
2014-05-16 17:48 Reply Reply with quote Quote
Permalink -2 VRB
i need a formula based on a child's birthdate how old he will be on September 1, 2014 (or any other year). cannot finagle the formula =DATEDIF(A13,TO DAY(),"Y") to not be TODAY, but 9/1/14. can someone help. I'm sure it's easy :)
2014-06-03 20:04 Reply Reply with quote Quote
Permalink 0 Thom H
Hi VRB,

You could either put the date in a separate cell and reference that Cell instead of TODAY(). This would mean you can change the date as you please without having to risk breaking the Argument (Function). Assuming the Date (01/09/14) is in Cell A1, you could use =DATEDIF(A13,TEXT(A1,"DD/MM/YYYY"),"Y")

Alternatively, if you want to just switch the TODAY() function with that date then you'd use this: =DATEDIF(A13,DA TE(2014,9,1),"Y ")
2014-06-04 18:55 Reply Reply with quote Quote
Permalink 0 Lucy
Hi,
May I know why when I try get for the next age, its become error?

28/06/1986 04/06/2014 27
"28/03/1987 04/06/2014 #VALUE!
"03/03/1942 04/06/2014 #VALUE!
"01/01/2000 04/06/2014 #VALUE!
"03/03/1945 04/06/2014 #VALUE!
2014-06-04 04:46 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Lucy,

I'd need more context as to what you're trying to set up, how it's done and your chosen function but it looks as though you've put the age function into the third column then you're dragging it down? If you check each function and just make sure the correct cells are referenced this should do the trick.

#VALUE errors tend to mean that you're using referencing cells that don't contain data in the format the function expects. In this case the functions are looking for dates so Excel may well have converted your dates into text format? Easy fix = highlight the date cells, right click, format cells, format as date.

If that doesn't work you'll need to create a secondary column next to each one (Which you can later hide) then use the =date() function to convert them into date format (see my reply to sanoj xavier further down the page for info on this function).

Thanks,

Thom
2014-11-17 12:50 Reply Reply with quote Quote
Permalink 0 Sandeep Sanjeevi
Hi,

This is very helpful and very clear to understand for any one.Helped me a lot.

Thanks,
Sandeep Sanjeevi.
2014-06-10 07:31 Reply Reply with quote Quote
Permalink -2 sanoj xavier
how do I convert 19610821 to age?
2014-06-19 01:06 Reply Reply with quote Quote
Permalink +1 thomh
Hi Sanoj,
you'll need to convert it into a correctly formatted date first. then you can use any of the above functions. I'd recommend either doing this with a function in the column next to it, then referencing that column in your chosen function.
If you're feeling confident enough in excel you could try making a long argument by putting the conversion function into the age calculation function.

here's an example of what you could use, assuming "19610821" is in cell A1

=DATE(LEFT(A1,4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))

let me know how you get on.

regards,
Thom
2014-11-13 08:19 Reply Reply with quote Quote
Permalink 0 aamir khan
for example if date12/6/1983 i want to calculate the date month year till 1/1/2014 what will be the formula? please help me..
2014-06-28 15:55 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Amir,

This is covered in Method D (Using your first date instead of the DOB and you'll need to take the today() functions out of method D and replace them with your end date, or a cell reference for the cell containing the end date, so if in your example you have 12/06/1983 in call A1 and 1/1/2014 in cell B1 then in cell C1 you could put:

=DATEDIF(A1,B1,"Y") & " Years, " & DATEDIF(A1,B1," YM") & " Months, " & DATEDIF(A1,B1," MD") & " Days"

Don't forget that if you have a list and you drag this function down then the cell references will auto update as you pull them down i.e A1 will become A2 then A3..

If you have lots of start date but the end date will always be the value of call B1 then you can lock that cell reference (Make it absolute) but using Dollar signs so instead of writing B1 you'd put $B$1

Thanks,

Thom
2014-11-17 12:57 Reply Reply with quote Quote
Permalink 0 hk
Hi, how would I incorportate a text field form into this formula(DOB bookmark referencing the DOB in this formula) into a MSWord 2010 document? I've also tried creating a table in MSWord with 2 cells (DOB in A1 and formula in A2) and I get a syntax error. Here is my formula (with and without the parentheses at the beginning and end gives me the same error). A1 cell contains "7/1/2010":

=(DATEDIF(A1,TODAY(),"Y")& " Years, " &DATEDIF(A1,TOD AY(),"YM")& " Months, " &DATEDIF(A1,TOD AY(),"MD")& " Days)

Thanks
2014-07-02 15:48 Reply Reply with quote Quote
Permalink -2 satheesh
Why datedif formula not functioning in ms excel 2007?
2014-08-03 23:57 Reply Reply with quote Quote
Permalink 0 thomh
Hi Satheesh,
It was added around about then so it should work, but you won't get any autosum assistance, you'll have to type it blind. personally, I tend to avoid autosum anyway (once you get into advanced functions it just gets in the way)
thanks,
Thom
2014-11-13 08:24 Reply Reply with quote Quote
Permalink -2 CI
Hi, If I use the formulas provide I would need the age to not advance even if they have a birthday tomorrow. For example if I use the formula today 08/15/2014 and they have a birthday of 08/16/2014 I need the spreadsheet to essentially lock or remember the age on 08/15/2014.

Thanks
2014-08-15 16:21 Reply Reply with quote Quote
Permalink 0 thomh
Hi CI,

Put your chosen function into an =if() function like:
=if(CHOSENFUNCTION=CELLCONTAININGENDDATE,CELLCONTAININGENDDATE,CHOSENFUNCTION)
IF the function you choose is equal to the value in your chosen cell then it will return the value of said cell if not, it will keep calculating.
thanks,
thom
2014-11-13 08:34 Reply Reply with quote Quote
Permalink -1 ebeckin
Hi Thomh or any other experts,

I would like to be able to calculate an age (rounded down to years only) that is the difference between a particular date (the date a patient was referred) and their birthdate so that I end up with the age they were when they were referred by their GP. The date of birth is in field C2 and the referral date is in field D2. I'm not very good at Excel so I'm being specific about this so I can just copy/paste your formula. I've tried using FLOOR and ROUNDDOWN but I'm obviously doing something wrong. I can get a value like 7.8856 by entering =(g2-c2)/365 but if I remove the digital places to one, it rounds it up to 8, which I don't want. Can anybody help me, please? :)
2015-06-10 09:45 Reply Reply with quote Quote
Permalink -3 Thom h
Not entirely sure of what you're after but I assume you want it to round down thus giving the exact age? In which case you can use rounddown() or I believe floor() will also do the trick.
Thanks, thom
2015-09-26 15:46 Reply Reply with quote Quote
Permalink -2 JAGDISH BAUDH
hi how calculate age column wise exmp- 1- day 2- month 3- years
2014-08-23 06:14 Reply Reply with quote Quote
Permalink -2 Thom
Hi Jagdish,

Could you please clarify what exactly you need to do? Did you want to work out the date but roll it back by 1 day, 2 months and three years? if so, I'd go with method D (Date Difs) to get each one (year, months and days and wrap each datedif within an =sum() and minus the needed difference form each them. Happy to provide an example if you can clarify what exactly you're after :)
Thanks,

Thom
2015-11-16 16:56 Reply Reply with quote Quote
Permalink -2 Juliemichaela
Can anyone please help?! I have created an Excel spreadsheet. So far so good! Under the 'Client Details' tab are different headings including in column A: Status (the two options being 'client' or purely an 'enquiry'), column B: First Name, column C: Surname, Column D: Email Address and so on.
I have then created a different tab within the same worksheet entitled 'Enquiries'. I want to insert a formula in this tab that will automatically copy across from the 'Client Details' tab the whole line of data for all entries that have a Status of 'Enquiry' displayed in column A.
Can anyone please help?
Many thanks, Julie
2014-09-11 10:34 Reply Reply with quote Quote
Permalink -2 Carrie Pool
I have a death date and the exact age at time of death. I need to use this information to calculate a date of birth. Can anyone help me with the formula for this?
2014-10-11 20:11 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Carrie,

That's difficult because it may well be a year out without the EXACT age (81.0223 sort of format) as they may well have a birthday in December but you calculate it as at June, meaning it would calculate their age at 81 when in actual fact they're still only 80.

Assuming the age at death is in box A1 and the current date is in B1 (You could replace references to B1 with the =today() function if you'll ony use this ones):

=DATE(YEAR(B1)-A1,MONTH(B1),DAY(B1))

This calculates the given age from the year value of the current date.

Kind regards,

Thom
2014-11-17 17:30 Reply Reply with quote Quote
Permalink -2 Dai_Green
Hi all - Need a bit of help. Please??

I have date of births, and have used the DATEDIF formula to work out the Year and Month of each individual at 2 different points in time.

For Example In May I know Joe is 10 Years, 5 Month but he is only achieving as child of 8 Years, 10 Month. So I want to subtract 1 from the other. So I have an answer of how many Year, Month he is behind. Any Suggestions??

Thank you,

David
2014-10-20 17:11 Reply Reply with quote Quote
Permalink 0 Thom H
Hi David,
Please can you copy and paste in the functions you used and the results so that I know the exact format you're working with here?
Thanks
Thom
2014-11-18 08:18 Reply Reply with quote Quote
Permalink 0 AP
The Datedif function is very helpful& the best....... Thnks a lot
2014-11-12 05:41 Reply Reply with quote Quote
Permalink 0 Khalil ur Rehman
=(DATEDIF(A1,TO DAY(),"Y")& " Years, " &DATEDIF(A1,TOD AY(),"YM")& " Months, " &DATEDIF(A1,TOD AY(),"MD")& " Days)
is very helpful for me in my office.

Thanks
2014-11-13 17:50 Reply Reply with quote Quote
Permalink 0 David84*
Many thanks for this, just got me out of a tight spot at work! Stay classy, excel geniuses.
2014-11-21 11:37 Reply Reply with quote Quote
Permalink 0 Tanmay Chanda
Its Very helpful many many thanks.....
2015-01-13 11:08 Reply Reply with quote Quote
Permalink 0 Pedro
Its was very easy to follow and all the possible options for me to decide which was the best that accommodate my needs. Thanks :-) ;-) :lol:
2015-01-21 16:21 Reply Reply with quote Quote
Permalink 0 Manoj
Is there any way to convert age into date of birth. I have a sheet with name and age of customers as if age in years only. Is there any way to have DOB as result
2015-01-29 11:38 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Manoj,
You wouldn't really be able to get the exact date of birth, unless you knew how old they were exactly i.e. 20 years, 50 days. You could calculate it to year by doing something like =YEAR(TODAY())- A1 assuming their current age was in Cell A1.
Thanks,
Thom
2015-03-07 18:43 Reply Reply with quote Quote
Permalink 0 Carlos Somarriba
Great!!! All formulas works perfect. Thank you so much.
2015-01-30 14:57 Reply Reply with quote Quote
Permalink 0 B Kumar
Hi Thom,
Kindly Help me i am not understand this formula.
Please calculate my date of birth explained me my date of birth 15/09/1988
2015-02-12 18:44 Reply Reply with quote Quote
Permalink 0 Thom H
Hi B Kumar,
I'm afraid I don't follow your question. Could you please clarify for me?
Thanks, Thom
2015-03-07 18:45 Reply Reply with quote Quote
Permalink 0 rowena
Hi thanks! ;-)
Its abig help for me having your tips....
2015-02-21 12:42 Reply Reply with quote Quote
Permalink 0 Kimberley Woodward
hi what would the formula be to work out a child's date of birth in exact months (so 24/02/13 = 24mths?)

I am an early years teacher and we work in months not years and months.

Can you help?

Kimberley
2015-02-24 16:02 Reply Reply with quote Quote
Permalink +1 Thom H
Hi Kimberley,
Datedif would do that for you, Just use =DATEDIF(A1,TOD AY(),"m") Assuming the DOB is in Cell A1.
Thanks,
Thom
2015-03-07 19:00 Reply Reply with quote Quote
Permalink 0 Tanja
Hi,

How do I convert fx 25102002 into a date and than age?
2015-02-25 23:46 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Tanja,

I'm assuming that would be the 25th October 2002? Is the FX part of the box? If so, I'd start with =right(A1,8) which would give you the last eight digits from cell A1, so if Cell A1 contained "fx 25102002" it would simply return "25102002". Now to convert that 8 digit string into a palatable date, I'd use the =date() function. I've done one for you, you just need to switch the A1 with the cell that contains your function =DATE(RIGHT(A1,4),RIGHT(LEFT(A1,4),2),LEFT(A1,2))
Thanks, Thom
2015-03-07 18:52 Reply Reply with quote Quote
Permalink 0 Krista
Hello, I have a column in my spreadsheet for birth date, that is formatted as DD-Mon-YY (ie 11-Feb-03 for my son that was born on February 11, 2003). I used the =DATEDIF function referenced above and it is returning 115 Years, 2 Months, 11 Days. What is wrong and how do I correct it? Thanks for your help.
=DATEDIF(E13,TODAY(),"Y") & " Years, " & DATEDIF(E13,TOD AY(),"YM") & " Months, " & DATEDIF(E13,TOD AY(),"MD") & " Days"
Note: When I type =today() in a cell it is correctly listing 11-Mar-15 as today's date.
Also note: The regional settings in my control panel list short date as dd-MMM-yy, so I don't think this would be the problem.
2015-03-11 23:16 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Krista,
That would be the length of time since Excel's callander started :P

You're getting that because the cell you're referencing is blank - is you r son's DOB in Cell E13? Because that's the Cell it's calculating from.

Possible fixes:
- Your function contains errors, please see the corrected one below:
=DATEDIF(E13,TODAY(),"Y") & " Years, " & DATEDIF(E13,TOD AY(),"YM") & " Months, " & DATEDIF(E13,TOD AY(),"MD") & " Days"

- You need to use the format option to format it correctly as a date. i.e. to make it read in that format you'll need to go to the 'Custom' option under format and enter:[$-809]dd -mmm-yy;@ Under 'Type'

- If you're just typing it in like that, it probably won't read it as a date, you need to enter it as 11/02/2003

This will give you "12 Years, 1 Months, 1 Days"

Thanks,

Thom
2015-03-12 18:35 Reply Reply with quote Quote
Permalink 0 Krista
Oops. LOL I had drafted this long note to you telling you it was still not working, when I noticed you were right...cell E13 was empty. My son's DOB is in cell E15. Oops. LOL Thanks for your kind help. It's working now. :) Have a great day!!
2015-03-14 22:45 Reply Reply with quote Quote
Permalink 0 mario
Krista Check your E13 Column it must be blank, thats why you get 115 Years, change e13 to correct column number
2015-10-21 06:21 Reply Reply with quote Quote
Permalink 0 KELSEY
I like the "Method B" format when figuring out the age. I'm wondering if there is a way to have the years old and just the months old if younger than 2 using that format. Thanks!
2015-04-18 14:55 Reply Reply with quote Quote
Permalink 0 Thom H
Hi Kelsey,

Option B (DATEDIF()) only gives you the years as a number value - did you perhaps mean option D? I've added an IF statement to Option B, so that it only shows the months and yeards if the person is 2 or under:
=IF(DATEDIF(A2,NOW(),"y")
2015-04-20 12:14 Reply Reply with quote Quote
Permalink 0 raksmey
I really appreciate with this fomular.
:D
2015-04-22 03:40 Reply Reply with quote Quote
Permalink 0 Shephard
Hello,
I have added the formula for age that works well. I was able to continue the formula down the entire column which is what I needed. The issue is that a computed "age" appears in each cell despite the fact that I have not filled in a birthdate. ie: everything below the correct calculations in the column reads 115 years as a default. Is there a way to make the age cell blank until a value is placed in the birthdate cell so as not to see ongoing "115's?" Thanks in advance~
2015-06-22 23:08 Reply Reply with quote Quote
Permalink 0 Chris
Method D is exactly what I've been looking for, minus the day portion (easily removed). I've created an inventory of PCs for my company, and am using this to calculate hardware age based on purchase date. My question is this - is there a way to take this formula and make it so that I can essentially click a button to have it applied to a cell, and then just be able to then select the cell I want it to do the calculation on? Otherwise, I'm looking at having to copy/paste the formula to several hundred cells, and then go and manually change the target cell in each one.

Thanks.
2015-06-26 14:40 Reply Reply with quote Quote
Permalink 0 Thom h
Just create a macro to take the selected cell, and dump the Function next to it, referencing that cell. You'll need to look in another thread to find macro guidance. It just drag the cell down
2015-09-26 15:53 Reply Reply with quote Quote
Permalink 0 Steve Konz
Not sure if this was covered, but a lot of people right these and don't consider leap years when looking at the difference in days. Here is one I wrote that considers leap year rules (even for the 100 & 400 year rules)....


=IF((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),MONTH(A2),DAY(A2))),A1,"d"))=365,(DATEDIF(A2,A1,"y"))+((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),(MONTH(A2)),(DAY(A2)))),A1,"d")-1)/365),(DATEDIF(A2,A1,"y"))+((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),(MONTH(A2)),(DAY(A2)))),A1,"d"))/365))


where A2 is the date of birth, and A1 is the current date (which could be replaced with TODAY() also.

Please let me know if there are any flaws in this or simpliar ways to calculate it.
2015-06-26 19:15 Reply Reply with quote Quote
Permalink 0 Thom h
Excellent function, Steve. I believe datedif also does consider leap years :) yours looks very similar to something I wrote a couple of years ago add the client wanted to account for leap years and didn't trust datedif
2015-09-26 15:55 Reply Reply with quote Quote
Permalink 0 Steve Konz
If I recall correctly, but datedif wasn't the issue regarding leap years. Once you get the number of days difference and you need to identify a specific date at which they will turn an age is when you need to factor in leap days. The second formula I wrote below does account for those days. If you don't consider that in the formula when you forecast you will be off by a few days.
2015-11-11 17:14 Reply Reply with quote Quote
Permalink 0 Steve Konz
Here is one i have that accounts for leap days (well it excludes them pretty much)


=IF((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),MONTH(C3),DAY(C3))),C2,"d"))=365,(DATEDIF(C3,C2,"y"))+((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),(MONTH(C3)),(DAY(C3)))),C2,"d")-1)/365),(DATEDIF(C3,C2,"y"))+((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),(MONTH(C3)),(DAY(C3)))),C2,"d"))/365))

C2 = Current Date - could use TODAY()
C3 = DOB
2015-06-26 20:37 Reply Reply with quote Quote
Permalink 0 guru
It is Nice i think it good to learn to Excel 8) :roll: :P
2015-07-08 07:47 Reply Reply with quote Quote
Permalink 0 Atomicpetro
Another question in regards to age. How can I get the age in cell B2 [age?] when I have in Cell A2 [26/06/1966] and in Cell B1 [2013] just the year. Thanks.
2015-07-22 14:17 Reply Reply with quote Quote
Permalink 0 Thom h
Swap the reference to cell b1 with =date then you build the date with three values: year, month and day. You reference b1 add the year then you'll just have to put 01 as the year and month. Or you could steal the dates from cell b2 using =month and =day
2015-09-26 16:00 Reply Reply with quote Quote
Permalink 0 Steve Konz
Are you just trying to get the age they will be in the year of B1? If so, then just do B2 =(B1-(YEAR(A2)))

If that's not what you are looking for, can you explain what it is you need in more detail?
2015-11-11 17:41 Reply Reply with quote Quote
Permalink 0 Channing
Literally none of these worked. When I subtract, it just gives me another date like 03/03/1953
2015-08-07 14:51 Reply Reply with quote Quote
Permalink 0 Steve Konz
Channing,

Can you provide cell references and what data is entered in them along with your formula and that cell reference?

First thought is you should format your result as a number instead of Date. That might be your issue.
2015-11-11 17:10 Reply Reply with quote Quote
Permalink 0 Miranda Creed-Miles
Brilliant tips!
Really worked well! If you want the age to display as a single number on Method B - just format the cell to 'Number' with no decimal points - it works!! Hurrah!!
2015-09-16 15:50 Reply Reply with quote Quote
Permalink 0 Jaspreet
Hello
Retirement age is 60 years and last date of each month and the formula works, but in our case if an employee's date of birth falls on 1st of any month then he/she will retire on the previous months last date. (eg. employee 1 DOB is 03/01/1960 then output needed is 28-02-2020 and employee 2 DOB is 03/02/1960 then 31-03-2020)
2015-09-23 15:28 Reply Reply with quote Quote
Permalink 0 Gatewarden
I have about 100 cells with the calculated age as you done.
Is there any easy way to make groupings on all that are the age of 10 etc.

I have automated the document as we will have more people in all the time so I need automated groupings as well.

Any suggestions?

/Jacob
2015-10-30 13:28 Reply Reply with quote Quote
Permalink -1 Narsing rao K
how to convert date of birth in to worlds

02/02/1966
second february nineteen sixty six
2015-11-01 08:24 Reply Reply with quote Quote
Permalink 0 Thom
I have a feeling date functions may do this in later versions of Excel i.e. =month() etc. If not, you'll need to create a table with the number in the first column i.e. 1-12 and months in the 2nd column Jan-Dec) then use vlookups so that the function can convert the numbers into the correct text. A lot of fun to be had there as I once wrote a sheet that did this very thing, there was further issues involved in using the correct affix i.e. nd rd st or th. Again, lookup tables did that and I was able to shorten the funtions calculations by using if statements instead but that was very fiddly and half the time I found myself trying to use PHP which would do the job in no time! Have a look around online as someone's likely done some VBA that'll do the trick or KUTools probably have something.
2015-11-16 17:04 Reply Reply with quote Quote
Permalink -1 Subramanian K
In my Excel, I could not get the DATEDIF() function for whatever reason. Yet, I got a YEARFRAC() function which seems to do the same. Hope it is reliable.

Any comment anyone?
2016-01-07 13:02 Reply Reply with quote Quote
Permalink 0 Subramanian K
I couldn't find DATEDIF() in my Excel but YEARFRAC() did it. Hope it is reliable, any comment anyone?
2016-01-07 13:03 Reply Reply with quote Quote
Permalink 0 Thom H
Datedif is better, but was only introduced on more recent versions of Excel. I believe you can use it as far back as 2003, but it won't give you any autosum guidance (You can still use it though!)
2016-02-15 21:45 Reply Reply with quote Quote
Permalink 0 Velmurugan rengaraja
It is amazing to understand and very very useful.
2016-01-09 13:23 Reply Reply with quote Quote
Permalink 0 Beloved
Your teachings COULD be helpful but they are DEFINITELY NOT! Not because they are wrong per se but simply because 1. why do you use comma's (,) when Excel only accepts semi-colons (;) really beats me? 2. why do you put spaces in your examples when Excel NEVER accepts spaces?? Your work could be helpful but it confuses people instead. So, a perfectly good answer fails to produce the desired result practically on Excel because of YOUR carelessness. You spoil your own good work!...
2016-03-04 10:43 Reply Reply with quote Quote
Permalink 0 # Subramanian K`
Responding to Beloved 2016-03-04:2016 -03-04 in my yahoo mail box.Somehow, I thought they were not directed at me as I don't seem to have given details with commas, spaces, etc. I had only suggested the use of YEARFRAC() instead of DATEDIF function which i wasn't getting on my Excel. A clarification is welcome.
2016-03-07 04:47 Reply Reply with quote Quote
Permalink 0 Moon
=DATEDIF(B10,NOW(),"Y")

Work great for me thank you!!!
2016-03-07 15:41 Reply Reply with quote Quote
Permalink +1 Ranil Somarathna
This function is great. Very easy to understand. Thanks!
2016-04-02 10:05 Reply Reply with quote Quote
Permalink 0 Wendy
I want to find out the number of days from 8 May 2016 to 31 July 2016. Is there a formula for this?
2016-05-13 11:28 Reply Reply with quote Quote
Permalink 0 ANNYONG
my problem is that when I used the formula and drag it down it will just copy the value of the first cell. .

I have to enter it one by one.
2016-07-26 23:31 Reply Reply with quote Quote
Permalink +1 Krishna Gupta
6/4/1990 in A2

=TEXT(TODAY()-A2,"YY")&" Years, "&TEXT(TODAY()- A2,"mm")&" Months, "&TEXT(TODAY()- A2,"dd")&" Days"

use this simple formula and get answer as below :

26 Years, 03 Months, 15 Days
2016-08-17 10:33 Reply Reply with quote Quote
Permalink 0 Kishore Tholana
Thanks a lot for the formula. I got the desired result. Many thanks to the thread poster.

Regards,

KT
2016-09-30 09:08 Reply Reply with quote Quote
Permalink 0 Cedric
What formula can I use to obtain the invoice amount for a specific month from a list of months with balances.
2016-11-29 06:40 Reply Reply with quote Quote
Permalink 0 SUBRAMANIAN K`
Month Invoice Amount Assume cells A1 through B12 contain the 12 month
Jan 810 names and amounts to invoive as at left.
Feb 1200 For month Jan
Mar 850 Invoie Amount 810
Apr 930
May 1250 The formula entered in the cell above here
Jun 1300 framed above is
Jul 1100
Aug 820 =VLOOKUP(G3,$B$2:$C$13,2,FALSE)
Sep 750
Oct 875
Nov 980
Dec 1450

Cedric, please confirm this answers your query
2016-12-02 14:25 Reply Reply with quote Quote
Permalink 0 SUBRAMANIAN K`
Correction to what I just posted, the data is sitting in Cells B2:C13 (not A1:B12)
2016-12-02 14:40 Reply Reply with quote Quote
Permalink 0 Matt Viverette
One should be careful using Method C, with the YEARFRAC() function, because of rounding error in computations involving leap years. You will calculate the incorrect age for people born in a leap year.

Example:
John Smith was born on 6/5/1932. 1932 is a leap year. Compute John Smith's age on 6/5/2002. We would say John Smith is 70 years old on his 70th birthday, 6/5/2002. However, because YEARFRAC(DATE(1 932,6,5),DATE(2 002,6,5), 1) computes to 69.99795627, adding ROUNDDOWN computes to 69. In fact, John is 70. I'm not sure if this is the best solution, but I've added a precise day to the computation, which I assume shouldn't affect other calculations because it is shorter than the formula assumes a day to be.

(1/365.2422) is a precise day when accounting for leap years

My adjusted YEARFRAC is:

YEARFRAC(DATE(1 932,6,5),DATE(2 002,6,5), 1)+(1/365.2422) which computes to 70.00069418 and when combined with ROUNDDOWN, gives 70.

Putting it all together according to the references in the article:
=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1)+(1/365.2422) , 0)
2016-12-09 20:56 Reply Reply with quote Quote
Permalink 0 MAJID
DEAR SIR KINDLY HELP TO FIND OUT MY DATE OF APPOINTMENT OF SERVICE , MY SERVICE LENGTH IS 24 YEARS 6 MONTH & 5 DAYS ON DATE 24 DECEMBER 2016 KINDLY SUGGEST FORMULA TO FIND OUT DATE OF APPOINTMENT I SHALL REMAIN THANKFUL TO YOU.
2016-12-24 06:33 Reply Reply with quote Quote

Add comment


Security code
Refresh