Calculating age in years and months from Date of Birth

I think these formulas help me a lot, but I need the answer in years an
months, not decimals or days! Can you help?

To calculate the number of years from the date, enter the followin
formula in cell B1: =YEAR(TODAY())-YEAR(A1)

The result (rounded): 23 Years 

For a decimal solution, use the following formula
=DATEDIF(A1,TODAY(),"m")/12

The result: 23.25 Years


To calculate the number of months from the date, enter the followin
formula in cell D1: =DATEDIF(A1,TODAY(),"m")

The result: 279 Month

--
Message posted from http://www.ExcelForum.com

0
2/4/2004 9:54:25 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
859 Views

Similar Articles

[PageSpeed] 6

Hi
try
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months, " & DATEDIF(A1,NOW(),"md") & " days"

HTH
Frank

> I think these formulas help me a lot, but I need the answer in years
> and months, not decimals or days! Can you help?
>
> To calculate the number of years from the date, enter the following
> formula in cell B1: =YEAR(TODAY())-YEAR(A1)
>
> The result (rounded): 23 Years
>
> For a decimal solution, use the following formula:
> =DATEDIF(A1,TODAY(),"m")/12
>
> The result: 23.25 Years
>
>
> To calculate the number of months from the date, enter the following
> formula in cell D1: =DATEDIF(A1,TODAY(),"m")
>
> The result: 279 Months
>
>
> ---
> Message posted from http://www.ExcelForum.com/


0
frank.kabel (11126)
2/4/2004 10:05:24 AM
Frank   Brilliant thanks!

Davi

--
Message posted from http://www.ExcelForum.com

0
2/4/2004 11:01:57 AM
Reply:

Similar Artilces:

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

Set a recurring task ie. a task to check a website each month
It would be advantageous to be able to set a recurring task ie. a daily, weekly, monthly or other multiple task. An example would be if you needed to go to an accounts website or prospects website once a month to look for leads. Or to check out a competitor for new things on their website. It could also apply to phoning or mailing etc. This would save constantly having to remember to create a followup or write up to dozens of followups at different dates. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote f...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Unable to calculate STD DEV for any value with 6 or more digits to the left dec.
I am unable to obtain the standard deviation for any value with six or more digits to the left of the decimal. The values are coordinates, usually with six or seven digits to the left of the decimal and three to the right. Found four types of standard deviation in the Microsoft Excel help menus. 1) STDEV - will work with values up to five places left of the decimal point. 2) STDEVA - will work with values up to five places left of the decimal point. 3) STDEVP - will work with values up to five places left of the decimal point. 4) STDEVPA - will work with values up to five places left of...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Reprint year end closing journal for GL
We had a printing problem when I did our system year end so nono of the posting journals printed. I could reprint the BBF journal but I can not find the the journal that clears out the P&L accounts to the retained earnings. Is there a way to reprint this? Thanks -- Pieter ...

Average starting with first month
I have a large worksheet of sales data where products are down the rows and months are across columns. I would like a formula to calculate the average monthly sales but only inlude those months starting with the month of first sale going forward. If a sale month is zero the cell is blank Example worksheet: Mo1 / Mo2 / Mo3 / Mo4 / Mo 5 Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months) Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero) Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of zero) ...

Modifiying Aging
Hi, Wondering if anyone knows of an easy way via SQL Statements to modify RMS' aging for all customers to "Net 30 Days" instead of "Revolving". For whatever reason, when we had the system seutp/installed 2+ yrs ago, the manager at the time opted to configure the system for Revolving but we really need it to be Net 30 Days. Any help would be greatly appreciated. Tracy B Tracy, If you can submit both the Account Codes from the Net 30 Days account and the Revolving account I can whip up a script for you to do this. Thank you, Ryan Sakry Program Ma...

Public Store Age Limits
I installed SBS 2003 Standard Edition. I moved public folders from an old Exchange 5.5 server to a .pst file and then from that .pst file into the public store of this 2003 Exchange server. Exchange set the Age limits of all these folders to 7 days. So all the e-mails from over 7 days ago are gone from all the folders with the Public store. In system manager, servers, server name, first storage group, public folder store, public folder instances, it shows as a title bar removed older than and the date of 2/10/2004. I changed the age limits for all folders to use public store default and made t...

Looking up a date
Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

Year-End Adjustments by going into database
I am creating year-end reports and discovered that some sold items have $0 cost, no item number, or supplier name on Sales Report. What happened was that at the time of these transactions, I created the item on the fly and I forgot to enter the cost and/or suppliername. I entered the cost and supplier info after the transctions. I went into the database and I could not update COSTSOLD in ViewItemMovement. What do I do? ViewItemMovement isn't a table, it's a view - the underlying data resides in other tables. The particular field you're looking for is in TransactionEntry. ...

Format Date 05-28-10
Hi, all, How do I format the date to show May, 2010? The closest I can get is May-10 from the drop down list. Thanks, Malcolm Malcolm wrote: > Hi, all, > How do I format the date to show May, 2010? The closest I can get is May-10 > from the drop down list. > > Thanks, > Malcolm Custom date format: mmmm, yyyy Custom Format mmmm, yyyy Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:05:01 -0700, Malcolm <Malcolm@discussions.microsoft.com> wrote: >Hi, all, >How do I format the date to show May, 2010? The closest I can get is M...

Retrieving emails two years ago
Okay, I may be way off here... I replaced the HD on my boss' computer and inadvertently lost his archived emails from 2004 - 2006. Is there a way to recover these emails from the first admin store? We're running SBS 2003 and using Outlook 2003. In news:9ADA1373-2D5D-4135-BA83-3F42E44344D7@microsoft.com, Dano <Dano@discussions.microsoft.com> typed: > Okay, I may be way off here... > > I replaced the HD on my boss' computer and inadvertently lost his > archived emails from 2004 - 2006. > > Is there a way to recover these emails from the first admin store...

Excel Date Formatting
Hi, In Excel the regional settings are set to short date format. However, if you try and format cells individually with a date, the date changes, but when the spreadsheet is closed and the reopened the formatting reverts to the short date format! We are using Excel 2000 with sp3 on XP (If thats any help!) If anyone has any ideas why this is occuring i'd love to know! Cheers, Hayden Are you saying that the same person on the same pc opens the file and the date format has changed? If it's another user (or another pc), then it could be the way excel picks up the date format if ...

SRS Default reports currency and date format
Hi there I have installed the SQL Reporting Services default reports supplied by Microsoft, which is very useful for the customer. However, I would like to know how to I change the date format and currency symbol on the reports. It currency displays the currency as '$', but we are in South Africa and the the date format to dsiplay dd/mm/yyyy and the currency to be 'R'. My regional settings are set correctly and the data within Dynamics displays correctly. Regards Robin Robin, You will need to change the Properties of the fields in question on the report itself. Ope...

Adding a line after a date
I have a file that I am trying to sort and add a line between years. This file is 36,000 lines long and holds multiple records by a specific identifyer. The problem is I have multiple records per year and I only want the file to put a break after each year not each date. Please help! :-) Thanks in advance Use a helper column with this formula copied down, then Copy > Pastespecial > Values to get rid of the formula......then do your line-insertion based on this column..... =YEAR(A1) Vaya con Dios, Chuck, CABGx3 "Mpls501sMan" <Mpls501sMan@discussions.microsoft....

Automatic/Manual Calculation
Using Excel 2002, I am set to Automatic calculation. One, and only one file always indicates it requires Manual calculation. I am sure it is something within the file, but am not sure what to look for. Can someone give me some guidance on what steps to take to correct the problem. Thanks, as always. Paul open the file, turn calculation to automatic, save the file. "PA" <PA@discussions.microsoft.com> wrote in message news:51D2D113-F067-4D81-A62F-255ECE8A9D08@microsoft.com... > Using Excel 2002, I am set to Automatic calculation. One, and only one file > always ind...