Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2020, 04:49 PM
Marcia's Avatar
Marcia Marcia is offline Join dates with custom format and calculate age Windows 7 32bit Join dates with custom format and calculate age Office 2013
Expert
Join dates with custom format and calculate age
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Reply With Quote
  #2  
Old 04-04-2020, 09:54 AM
BobBridges's Avatar
BobBridges BobBridges is offline Join dates with custom format and calculate age Windows 7 64bit Join dates with custom format and calculate age Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 04-04-2020, 03:52 PM
Marcia's Avatar
Marcia Marcia is offline Join dates with custom format and calculate age Windows 7 32bit Join dates with custom format and calculate age Office 2013
Expert
Join dates with custom format and calculate age
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 04-05-2020, 11:29 PM
ArviLaanemets ArviLaanemets is offline Join dates with custom format and calculate age Windows 8 Join dates with custom format and calculate age Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

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.
Attached Files
File Type: xlsx Age.xlsx (11.6 KB, 2 views)
Reply With Quote
  #5  
Old 04-06-2020, 11:01 AM
BobBridges's Avatar
BobBridges BobBridges is offline Join dates with custom format and calculate age Windows 7 64bit Join dates with custom format and calculate age Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #6  
Old 04-06-2020, 03:26 PM
Marcia's Avatar
Marcia Marcia is offline Join dates with custom format and calculate age Windows 7 32bit Join dates with custom format and calculate age Office 2013
Expert
Join dates with custom format and calculate age
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Join dates with custom format and calculate age 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft