# Formula To Calculate Age Based On Date Of Birth

Dec 10, 2009I 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.

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.

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).

I am building a spreadsheet that has everyone's birth date on it. If the person is not 22 years of age or older I would like for the cell text containing their birth date to be highlighted in RED.

View 9 Replies View RelatedI am using the following formula:

=DATEDIF(F7,TODAY(),"Y")

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.

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 ozgrid.com: =DATEDIF(H4,TODAY(),"y")&" years "&DATEDIF(H4,TODAY(),"ym")&" months "&DATEDIF(H4,TODAY(),"md")&" days"

I'm looking for a function that calculates a fee deduction based on the 28th of each month.

I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...

A

B

C

D

1

£1,300.00

=TODAY ()

2

-£200.00

28/02/13

FUNCTION

How to do it but basically I'm trying to put a formula in D2 as follows:

If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2

How to continue it calculating reductions per month by duplicating the formula...

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?

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)????

http://www.cpearson.com/excel/datedif.htm

--

"malvis" wrote:

> Is there a formula that can calculate age when given only 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 9 Replies View RelatedI'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.

Example

Initial Date :- 1st of January 2014

Frequency :- every 5 weeks

Current Date :- 9th of March 2014

Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)

I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)

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.

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

I am looking for a formula that calculates an individuals exact age given their date of birth. That shows just the age in years.

View 9 Replies View RelatedI 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 2 Replies View RelatedI 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 9 Replies View RelatedI 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.

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.

=IF(AND(Startup!$C64<="March",Startup!$D64=2008),Assumptions!$S17*P$3,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?

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.

how do you calculate someone's age nearest today

View 3 Replies View Relatedif I've worked in the company for 9.0384 years, how can I calculate that my start day was 04/20/04?

View 3 Replies View RelatedI 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.

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M

31-Dec

T

1-Jan

W

2-Jan

TH

3-Jan

F

4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M

28-Jan

T

29-Jan

W

30-Jan

TH

31-Jan

F

1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M

29-Apr

T

30-Apr

W

1-May

TH

2-May

F

3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

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.

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 2 Replies View RelatedI 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(),

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 ....

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.

1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)

2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.

table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F

2=S-S

3. How to write an If statement that assign a value to time based off this chart:

table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)

2=PM (15-2259)

3= MN (23-659)

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"))

i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.

View 9 Replies View RelatedI have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.

View 2 Replies View RelatedI need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:

KEYPerformance

2010,000

2120,000

2230,000

2340,000

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

201000.10.20.30.4

211000.10.20.30.4

221000.10.20.30.4

231000.10.20.30.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????

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.

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C

7/1/2012 0:00825-CL-A-41091-REG4.00

7/1/2012 0:00825-CL-A-41091-REG6.25

7/1/2013 0:00825-CL-A-41456-REG4.25

7/1/2013 0:00825-CL-A-41456-REG6.50

1/1/2014 0:00825-CL-A-41640-REG4.25

1/1/2014 0:00825-CL-A-41640-REG6.50

3/1/2014 0:00825-CL-E-41699-REG3.00

3/1/2014 0:00825-CL-E-41699-REG4.00

I have a scorecard that looks something like this:

90% or greater=3

80%-89%=2

70%-79%=1

0%-69%=0

I need the "cell" to react accordingly and I'm lost.

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?

ratemayjunejuli

$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

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 http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.

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?

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.

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%

ADVERTISEMENT