#1
|
||||
|
||||
Join dates with custom format and calculate age
Hi. I am helping our social worker in preparing a masterlist of beneficiaries for the govt amelioration assistance (Php5,500.00 or $100) to the vulnerable families (COVID19). The form requires us to separate the birth month ("0#"), birth date ("0#) and birth year in three columns. To fast track the encoding of the year, I formatted it as "19##" so only the last 2 digits are supplied. If the birth year of a beneficiary falls in year 20 not 19, I change the format to general.
After that we need to calculate the age so I joined the birthmonth date and year to get the full birthday in a helper column. The formula =INT(YEARFRAC(Birthday,DATE(2020,3,31))) however returns the value of 5 as the age of a person whose birthday is 9/9/25. The date (birthday) is formatted as dd/mm/yyyy. How do I correct the formula? Thank you. |
#2
|
||||
|
||||
I wouldn't go about it like that. Excel is really good at understanding dates, and I take it you're saying the original date is already in a solid date format; you just need to calculate the age of a given subject, right? So you have a birth date, eg 1954-07-16, or in your case 07/16/1954, and you want to know how old that person is? I'd do it this way: Assuming the birthday is in A1, =TODAY-A1. That gives you the person's age in days; if you want years, you can say =(TODAY-A1)/365.
One problem: You say the birth date is in the format mm/dd/yyyy, but you give as an example "9/9/25", which is not in that format. So which is it? |
#3
|
||||
|
||||
Bob, the result of the joined date in the birthday column was 9/9/25 even if I selected the date format as dd//mm/yyyy.
|
#4
|
|||
|
|||
Hi
In attached file is an example how I'd do this (if I understood correctly what you did want to do). I added an optional column where birth date (NB! Not Birthday!) is calculated too. User has to follow a simple rule (all years less than 1930 must be entered with full year number). When it is too difficult for user(s), then it will be best to make full year number mandatory (i.e. set up data validation rule which restricts entering year numbers less than 1900). With all other solutions you'll risk to err with century for year range 0-29. |
#5
|
||||
|
||||
Marcia, I haven't looked at Arvi's solution yet, but I think I must have misunderstood your original post. I thought you had a worksheet with a birth date, and you were trying to parse out the year, month and day in order to calculate the victim's age. Now that I read again, I think you're saying your worksheet has the birth date in separate month, day and year columns, and you're trying to reassemble them into a date so you can calculate the age. Is that right?
If so, assuming month is in A2, day in B2 and year in C2, I would try this: The birth date is =DATE(C2,A2,B2). The age in days is =TODAY()-DATE(C2,A2,B2), and if you want it in years (as of course you do) it can be =(TODAY()-DATE(C2,A2,B2))/365. That's assuming a full four-year birth year. If you have only two years, then you have to make some decision about the year—that is, if the victim is born in "19" is that 2019 or 1919? You'd want to include something like =IF(C2<20,2000+C2,1900+C2). And if I'm ~still~ not reading carefully, I apologize; try to explain again. |
#6
|
||||
|
||||
Thanks Bob and Arvi. I'm sorry for not attaching a sample sheet on which the readers could try and find a solution to my request. The IF formula required a helper column but it's okay. BTW I added IF birth year is 2000, 2000.
I will try the validation list and file it for future reference. I filtered those years below 1930 and retyped the 4 digit years. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Default How to automatically Calculate number of days between two dates? | wcngu1 | Word | 6 | 10-24-2019 04:20 AM |
Calculate time over two dates | Diver | Excel | 3 | 07-22-2017 11:31 PM |
Is this possible? - Calculate and Display the date range between 2 specific dates | Premier-UK | Excel | 2 | 08-18-2014 05:43 AM |
IF Formula to calculate dates | Sophie1 | Excel | 2 | 04-23-2014 07:19 AM |
How to change number format of dates | ketanco | Project | 1 | 09-11-2012 02:34 PM |