Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Formula To Calculate Age Based On Date Of Birth

I need a formula to calculate age today based on a person's date of birth. I used to know this but I have not used it for awhile.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Dertime Current Age Based On Birth Date
I am using the following formula:


I have a about 1200 rows with a column of student's birth dates. I need to convert the dates to ages and have them inputted in the next column Ages. I ran the code in the 'Age' column and it worked for the first birth date. But I do not know how to copy the code to the rest of the rows so it will convert every birth date in each row.

View Replies!   View Related
Highlight Chosen Age Based On Date Of Birth (DOB) Calculation
how to make a cell to change color oatomatically accordant to Year and Month reflexted already in the cell. I have a spreadsheet with Names,Surnames,DOB and also a column that is given the AGE years months and days.

If somebody reaches 21 -1 -2 -3 months this should show different colors in the persons row in a particular column. The function used for the AGE is fund on =DATEDIF(H4,TODAY(),"y")&" years "&DATEDIF(H4,TODAY(),"ym")&" months "&DATEDIF(H4,TODAY(),"md")&" days"

View Replies!   View Related
Calculating Age When Given Only Date Of Birth

"malvis" wrote:

> Is there a formula that can calculate age when given only Date of Birth?

View Replies!   View Related
Calculating Age From Date Of Birth
how to calculate the age from the date of birth and was given these two responses

1) I may be way off here but all that coding seems a waste !

I have in cell A1 = BLANK <--- enter your DOB here

I have in cell A2 =NOW()

in cell A3 do = A2-A1

Format that to General

In Cell A4 do =A3/365

Format that to no deciamal places and you get the age - this one is adding a year to everyones age?

2) Alternatively, if the DOB is A1 :-

=INT(YEARFRAC(A1,TODAY(),1)) - This one does not work it is saying there is an error with the "yearfrac"?

It is also when I am trying to put these into a pivot table, the pivot table is listing these down to 5 decimal places( when I have formatted to zero)????

View Replies!   View Related
Using Reverse Date Of Birth To Determine Age
I'm trying to use a list of date of births to determine an age output.

However the date of births are all in a simple reverse formate, eg

19790822 would be 22nd August 1979

Ideally, i would take this number and the output would just be the persons age in whole years.

View Replies!   View Related
Calulate Age: Find Out Over Aged Students By Date Of Birth
I need to find out over aged students by date of birth - now()-age started to school: DOB 8/16/2000 , Now() Age started school is 8/30/2005. Student is in grade 1. should be age 6, however, he is 8. Answer should be a little over 2 yrs.

View Replies!   View Related
Calculate Persons Age Based On Current Date
how do you calculate someone's age nearest today

View Replies!   View Related
Formula To Calculate Age
i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A

View Replies!   View Related
Formula In / With Dropdown: Calculate The Average %age In The Attached Spreadsheet
Need formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.

View Replies!   View Related
Converting Date Of Birth To 'Term Of Birth'
Is it possible to convert a students DoB into their term of birth (ToB).

I.e. a student has the DoB of 1/1/2001 which would mean their ToB would be 'Spring'

I suppose what I'm after is...
If a DoB falls into one of three groups
1/1/year to 30/4/year = 'Spring'
1/5/year to 31/8/year = 'Summer'
1/9/year to 31/12/year = 'Autumn'

The year of birth is irrelevant, it's the month which determines the 'term of birth' (Spring/Summer/Autumn).

View Replies!   View Related
How To Calculate Age
to calculate the age from the format date of birth shown below.

SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Calculate Age Down To Milliseconds
where i can get this download for Excel?

My husband sent me this program in Excel where you type in your birthdate and not only does it give you your age to the millisecond, but it also gives you a list of traits the person has. For example, I was born on November 20, 1987 and this popped up:

Age in years 19.61
Age in months 235
Age in days 7157
Age in hours 171760
Age in minutes 10305599
Age in seconds 618335921
Age in Milli seconds 61833592095
Age in weeks 50097
You born on Friday

View Replies!   View Related
Calculate Age In Months
I need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.

This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.

View Replies!   View Related
How To Calculate AGE Automatically Using FUNCTIONS?
Hi to all. I am trying to calculate a list of person's age automatically using functions. Is that possible? I am using Microsoft Excel 2007. I try searcing the web and they say DATEDIF.... But i just can't find the function in Excel 2007!

View Replies!   View Related
Calculate Age With Userform Textboxes
I have a userform with three textboxes.

1. txtStartDate
2. txtDateofBirth
3. txtAge

What I would like is for the txtAge box to be populated once dates have been entered into the other two textboxes.

It needs to be the person's age as calculated between the Date of Birth to the Start Date.

View Replies!   View Related
Calculate Age In Years And Months
I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.

=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"

View Replies!   View Related
To Figure Date Of Birth
I have the date of death and the age at death in Years, Month, Days. What formula would I use to figure the date of birth?

View Replies!   View Related
Lookup Birth Date
I have got a database wherein it contains the details like Employee Name, Birth Date, Date of Joining, etc in Sheet1. Suppose, Employee AAA birth date is 30/Jul/1980. When on 30th July 2009, it should display the Employee Id in Cell C3.
How should I lookup for the current date & month from the Employee Birth Date?

View Replies!   View Related
Calculate The Percent Of People Within Age Range
In the demographics sheet, I have ages listed from row F2 to F31 with different ages. I would like to get assistance with a formula that calculates the percentage of people within these age ranges:


It should be separate formulas. I'm sure if I'm given the first and last ones that I could do the others myself. Also, if I needed to know the percent of males and females, would i use the same formula?

View Replies!   View Related
Count Of Names By Date Of Birth (DOB)
I am using Office 2007 - Excel

I have a list of students with associated Schools
For each High School -Based on students Date of Birth (DOB) I am trying to get a count for:
Students UNDER 16 DOB before 8/25/91 and for each high school students over 16 DOB after 8/25/91

I thought subtototals, countif , maybe a pivot table to break down by school then do the 2 counts -
but I am at a loss for a formula to do the count

I have attached a sample file but the actual file has thousands of students and 23 different High schools which is why I was trying for subtotals or Pivot

View Replies!   View Related
Highlighting Birthdays (inc Birth Year) Past Today's Date
I have a list of birthdays (in date-month-year format) and simply want to highlight them if the date and month are past today's date.

View Replies!   View Related
Date Of Birth And Joining Date, In The Format "mm/dd/yyyyy"
I have database of my entire staff with thr date of birth and joining date, in the format "mm/dd/yyyyy".

I want if the date of birth is equal to today()+1, that means if someone is having his b'day on 25may ,and today is 24 may so its cell colour should turn into red.

I know conditional formatting is to be used here. but bit confused with the formula.

I am trying with this
=IF(TEXT(C3,"dd-mmmm")=TODAY()+1,true,FALSE), but not working.

means I want if someones b'day is coming , I want to ge t informed earlier.

View Replies!   View Related
Calculate Year To Date Based On Known Month
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

View Replies!   View Related
Calculate Formula According To Date
I am creating a proforma income statement for a business plan and need the cell values to calculate (or not) depending on the startup date that is entered on another sheet.

The following formula works if the value of Startup!$C64 is "March", but if it's "February" it returns 0.


What I'm trying to do is have the cell on the income statement only perform the calculation if the startup date is earlier or the same as the month referenced in the formula.

Perhaps I can't use the "<" with text?

View Replies!   View Related
Calculate Numer Of Paychecks Based On Date Of Hire
I have a business and i run payroll for my employees twice a month (semimonthly).
Date of paycheck will be 16th and 31st.

So if employee was hired on say 7/5/2009 then this employee will have 3 paychecks as of today
(1st paycheck from 7/1/2009 to 7/15/2009
2nd Paycheck from 7/16/2009 to 7/31/2009
3rd Paycheck from 8/1/2009 to 8/15/2009)

i need to know the # of pay checks for each employee computed in Cell C3 to C7.

View Replies!   View Related
Calculate Holidays Remaing Based On Start Date
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.

View Replies!   View Related
Age Based Calculator
I am trying to put together a spreadsheet that works out values based on a persons age or service length.

So in I have the following data in columns starting at B4 and ending at k4:

Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service.

The formula I need to provide in l4, m4 and n4 comes from the following rules:

* 0.5 week's pay for each full year of service where age during year less than 22
* 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41
* 1.5 weeks' pay for each full year of service where age during year is 41+

So in l4 I have:


View Replies!   View Related
Formula That Would Calculate What Workday A Date Would Be
I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

View Replies!   View Related
Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.

Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.

View Replies!   View Related
Formula To Calculate Based On Criteria
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.

The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.

I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.

View Replies!   View Related
Date/calculate Elapsed Time Formula
I have been asked to make some ammendements to a workbook currently in use.

I have to calculate elapsed time, and my formula is giving me odd results. I have a start time (cell B5), and an end time (cell F5). The formula being used was =IF(F5>0,((F5-B5)*24)) which gave the correct result. The cell is formatted as general. It didn't alot for the situation where the end time has not happened yet.

I changed the formula to =IF(F5>0,((F5-B5)*24),(K5-B5)*24) where cell K5 is the current time. The results are coming out with 6 - 9 decimal places. It makes no sense as I've tried setting the times to be exactly 24 or 48 hours to the minute. I've also tried replacing K5 in the formula to now(),

View Replies!   View Related
Formula To Calculate A Fee Based On Performance
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:


I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEYMin FeeFee 1Fee 2Fee 3Fee 4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999

So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

View Replies!   View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.

Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.

Example spreadsheet may be viewed at

View Replies!   View Related
Calculate Colum Total Based On Formula
I would like to have something that looks like a running total, but isn't.

I have in a colomn 'rate' and after that a column for each month in a year.
I would like to have at the bottom a total cost, so rate x hours.
have a look at the picture.

is there a formula for this?
$10 2,02,01,0
$12 2,03,00,0
$10 3,02,05,0
$15 4,02,53,0

total cost $134,0 $113,5 $105

View Replies!   View Related
Calculate Formula Based On Cell Criteria
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.

View Replies!   View Related
Formula To Calculate Date End Of Prev Month
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.

I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.

where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.

I would like a formula that will calculate these previous month end dates correctly.

See example below ....

View Replies!   View Related
Conditional Formula: Calculate The Difference In A Date Between Two Dates
I'm trying to make a formula to calculate the difference in a date between two dates. For exaple, in my business we have a due day for something. So the due date could be Feb 10, we need to know if it was done on time, late, or early. So I've gotten it to display late by doing this: =IF(B2>A2, "Late", (A2-B2)) But then I can't figure out how to incorporate this: =IF(B2=A2, "On Time") or =IF(B2<A2, "Early").

I would like to displaly this all in one cell. So if the due date is Feb 10 and it's turned in Feb 8 it's early, turned in Feb 10 it says on time, turned in Feb 11 it's late. I also tried this but it didn't work: =OR(IF(B2>A2,"Late")*IF(B2=A2,"On Time")*IF(B2<A2,"Early"))

View Replies!   View Related
Age Group Formula
I need to find the age group from the date of birth using excel. Eg.

if some one born between 1st Sept 1998 and 31st of August 2000, they come under 12.

if some one born between 1st Sept 1996 and 31st of August 1998, they come under 14. and so on.

View Replies!   View Related
Age: Formula Or Vba
I have a worksheet which calculates the amount of time an employee has been with our company. I currently use datedif, but i am unable to sort properly using this method. Is their a way to change the output using datedif from m,d,yyyy to mm,dd,yyyy? If not, anyone have a vba solution?

View Replies!   View Related
Formula To Determine Age
formula I can use to determine age between date of birth (in one cell) and an event date (in another cell)., i.e. birthdate 3/15/2000 with an event date of 3/2/2007.

View Replies!   View Related
Formula - Age Groups
i am setting up a work sheet on ages for a sporting team

i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group


14-3-00 would go to 7 yrs then go to under 8's

View Replies!   View Related
Mega Formula To Calculate Results Based On Several Conditions
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.

For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)

The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%

View Replies!   View Related
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example

Hours ST OT Total Hrs Gross Pay

8 2 10 ?

i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?

View Replies!   View Related
Formula With If Condition: Calculate Bonus Based On Productivity
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.

View Replies!   View Related
Formula To Find Age Groups
I have a column with customers ages in it. Now what I'd like to do is create a formula that checks all the age cells, then gives a percentage for each age group of my choosing. I tried some IF formulas but I just can't figure it out

View Replies!   View Related
Count Age Range Formula
I have a 2007 spreadsheet where a person's age is entered into a cell. I need to count how many are between 26-30, 31-35, 36-40, etc.

View Replies!   View Related
Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places

For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.

Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.

I included an example of what i mean in this excel file which should clear up any confusion.

View Replies!   View Related
Returning A Formula Based On A Date
I have a spreadsheet with 2 worksheets within it. These are simply called sheet 1 and sheet 2.

On sheet 2 I have cell A1 containing =(TODAY())
Also on Sheet 2 I have cells A28 to A39 containing Month names and cells B28 to B39 containing values. Like this

January 1000.00
February 1000.00
March 2500.00
April 1250.00
May 1750.00
June 2200.00


How can I return the sum of these values into Sheet 1 B18 based on the date. That is to say that I want to add up the sum of Jan, Feb, Mar, Apr, May up to and including callcualtaions made on 31st May but as soon as the date goes past 1st Jun I'd want to include June as well and so on.

View Replies!   View Related
Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.


cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View Replies!   View Related
Formula To Find Date Based On A Cell Value
First one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.

Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.

Example workbook is attached

View Replies!   View Related
Copyright 2005-08, All rights reserved