How to Properly Calculate Age from Date of Birth in Microsoft Access and Excel. Don't Use DateDiff!
HTML-код
- Опубликовано: 20 июл 2024
- Learn the CORRECT way to calculate someone's age in Microsoft Access. I've seen a ton of WRONG tutorials out there that just use DateDiff. No! That's not correct. DateDiff only looks at whole years. If the person's birthday hasn't passed for this year, they will show as older than they are.
Jimmy from Amherst NY (a Silver Member) asks: I saw a tip on RUclips a few days ago that showed how to calculate someone's age using DateDiff, however if I type in my birthday which is in December, and it’s currently November, the calculation is not right! I need an accurate formula for determining age. I run a nightclub and obviously we don't want to serve alcohol 20 year olds!
BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you an even better way to calculate age using an advanced formula. The method I show in the free video is 99.9% accurate. The formula in the Extended Cut is 100% accurate.
MEMBERS ONLY VIDEO:
• How to Properly Calcul...
BECOME A MEMBER:
/ @599cd
LINKS:
Get on my Mailing List: 599cd.com/YTML
DateDiff: 599cd.com/DateDiff
DateSerial: 599cd.com/DateSerial
More with Date Functions: 599cd.com/LBTM
Advanced DateTime Seminar: 599cd.com/ACDT
ADDITIONAL RESOURCES:
FREE Access Level 1: 599cd.com/Free1
FREE Blank Database Template: 599cd.com/FRL2
FREE Customer Template Database: 599cd.com/XCDT
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/TechHelp
Learning Connection 50% OFF: 599cd.com/Connection
Survey: 599cd.com/survey
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/AccessForum
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
Email Me: amicron@gmail.com
FURTHER READING:
en.wikipedia.org/wiki/Year
tinyurl.com/yxe8838f
KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, ms access calculating age from date of birth, calculate current age using date of birth, how to calculate age in ms access, calculating age in access
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Thank you sooooo much! I discover you 1 week ago, and my database growing so fast because of you! mucho lovos! :P
Wonderful!
Because of you my life has change thank you for the loving of access coding. and base on this idea, I created the DOB+Age and DOH+YearofService. Boom perfect.. Your the man. Thank you so much. . .
You're very welcome. Glad to help.
Thank you!! Using this method are you able to display years + months, like if someone is 76 years and 3 months old?
Thank you for the video, it was just what I needed. How can I get the calculated age to update in the table field?
You really don't want to because it changes all the time, right? Calculate it when you need it. However, if you DO want to store it, you'll have to use an AfterUpdate event when the date of birth is changed AND whenever the record is opened. 599cd.com/AfterUpdate 599cd.com/OnCurrent
A big thank you to you man, it worked like a charm too.
You're welcome!
Hi Richard. Thanks for the tutorial. How do I display a record that calculates the age in years, months, and days. So ideally, the calculated age record should be displayed like "19y, 11m, 23d"
I've got a function for that: 599cd.com/HowLong
I learn a lot from your tech help videos. On a similar topic of Correct age, I would like to know how a certain data which is age related will change as the age progresses, eg. the depreciation rate changes as an asset anniversary progresses. viz. 20 years age depreciation is 0.0750 and at age 21 the decreciation changes to 0.0802, how do you do that, with or without vba.
When you make those calculations, look at the age and apply the correct value - either with a calculation or a DLookup from a table.
This is beyond awesome!
You got it!
Thanks a million. I gave up months ago on figuring it out.
Glad I could help
Thanks for the information
My pleasure
Thank you for this, I've tried several ways to calculate age and your was the best, however it doesn't work for anyone born before 1950. I have an employee that was born in 1943 and his age shows up as -23. Is there a way to fix this? (I tried it from 1949 and earlier and it always gives a negative number in the 20's)
Thanks for any help!
I have a feeling you're running into a 2-digit year problem. Always type in your years with 4 digits. Windows controls that "cutoff" year for when you enter a 2-digit year. One of my members asked a similar question the other day. A video is coming up explaining this soon.
I made a DB years ago and used this to calculate age: =DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")
Yeah... well... if it works, it works... until it doesn't. LOL
Question,
I have just started learning microsoft access so please correct when am wrong. Heres my question. If i want to calculate the age of a person. Do i have to create a table with two field/column. One marking it as Date of birth and the other marking it as todays date. In order for me to get the correct age.
What if the year changes to 2022 would the age it automatically change in microsoft access.
You don't store today's date. Use the DATE() function which will always update, like I show in the video.
How do I copy one column contents to another column on same table. I could just do manually and paste on the another blank column ActualAge blank field. I want to do this via update query. Note"the column CalculateAge i a calculated column with expression: example: Calculate age:[Date()-[DOB] blah, blah". And then I want this Calculateage column to be copied to the another Actual Age column that is blank which is a real field [ActualAge]. I could use the ActualAge field as a calculated field, but I am not allow to change this field with expression. Can I use update query to update from expression build column to field[ActualAge] column?
Yep. You answered your own question. Use an Update Query. :) 599cd.com/UpdateQuery
How to automate group age. I set upt n a way, but it's too long the statement. I type the DOB and the age column is fed automatically, but I want to do the age group to be fed automatically. My formula IIF is too long. example: IIF(...[Age] is between 10 and 20, [Groupage]=10-20, and so forth, it became so long and very wordy. and worked, it pops the groups like a charm. Can I do this via LOOK UP method? with that, I want to replace the old method that is combobox to pick the group that was created by somebody else. But using the combobox clicking over and over for many data HURTS my hands. I need everything automated. Thank you.
Sure, set up a table with your age groupings and use DLOOKUP. 599cd.com/DLookup
Hello,
I have a quick question…
I want to setup a field on a form to do a simple calculation but I’m getting the syntax wrong. 😩
If I do =2023-ModelYear I get the proper result. But, I’d like to use a current year function instead of 2023 so it will be valid in subsequent years. I’ve tried Year() and Year(date) but not getting a number, so I’m doing something wrong! Thanks in advance.
599cd.com/qq
thank you
how can i calculate the age like ( day - month - year )
Works exactly the same way. A date is a date in Access. You can DISPLAY it differently based on your Regional Settings (Windows Control Panel).
Thank you for a excellent tutorial on calculating ages. However, I am unclear why using the following DateDiff function does not correctly calculate a person age. I used the following DateDiff formula DateDiff("d",[BirthDate],[CurrentDate])/365.2425 to run some experiments using the dataset provided in your video and the results were identical until the 13th decimal place. I agree using the year parameter (YYYY) for interval will not produce a correct age, but I am unsure why one could not calculate age using DateDiff with its "d" interval parameter other than taking more clock cycles. Since I only play with Access as hobby, your guidance on the matter would greatly appreciated.
The results would be the same. Why do you suspect it takes more clock cycles to do a simple math equation than to use the DateDiff function? If anything, I would think the DateDiff function would be more resource intensive.
@@599CD I agree that DateDiff would take more clock cycles than the simple simple subtract and division.
My question has do more with the assertion in the video Description that using DateDiff to calculate age is not correct way to calculate a person age. I need some help understanding why using DateDiff("d",[BirthDate],[CurrentDate])/365.2425 will not deliver the correct ages whereas ([CurrentDate]-[BirthDate])/365.2425 does, everything being equal. I need your help in understanding why the assertion in the video description is true. Thank you for the wonderful videos you create for both Access and Excel.
I have a table with patient data. One of the fields is date of birth. Another field is age. If there a way to create the formula and use it as a calculated field?
To do what, exactly?
Thanks! Excellent
You're welcome!
Can this be used in a form as the control source rather than making a query?
Sure... Try it Yourself: 599cd.com/Try
Hey ! I need to calculate an age at a specific date ( 07/05/1995)
My formula doesn't work : ( [DOB]+#7/5/1995#)/365,25
How can I calculate it ?
Thanks !
That's because your formula is wrong. I literally show you this exact example in the video. Did you even watch it?
Thank you
Welcome
We were having an issue with this calculation. Occasionally it was generating the incorrect age. One year younger. DOB = 3/22/1960, current date = 7/31/2022. A sharp eyed coworker notice that when we entered a leading zero to month of the "current date", 07/31/2022, it created the error. No leading zero 7/31/2022, calculated the correct age.
That's really weird. I've never seen that before.
Is there a way to calculate the converse? DOB from age?
I'm sure it's possible, but you would need an EXACT age in order to figure out someone's DOB. You'd need to know years, months, days, or an exact figure like 26.8763 years. Just a matter of math at that point.
Please help me sir
I have to age something like this. Example:
5 years 1 day = 6 years,
5 years 11 months 30 days = 6 years.
If one day goes up, then the year should change. Plz sir
I am not getting done with this formula
Age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")
Just calculate the age like I showed you and add 1 to it.
How to calculate exact months and days from the two dates in Ms access query
599cd.com/HowLong
very helpful :)
Thanks
Is there a way to create an age like in excel form
=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"
Sure. You can use DateDiff, but this way is easier. 599cd.com/DateDiff
@@599CD See on excel, you can use "YM" and "MD", there is no function on access for that.
Write an algorithm and draw a flow chart to calculate the age of the person from date of birth please arjent sir
No thanks.
I love you. This is awesome. Exactly what I was looking for... Thank you so much. Can I pay you a coffee?
That would be sweet! Thanks. 599cd.com/TipJar
very good
Is there a way to show the age with 2 decimals? I use this to show the age: Int((Date()-[Regdate])/365,2425) but Int show no decimals.
INT is going to round the value to zero decimal places. This is how you can check their calendar date (legally turning 21 at midnight, for example). If you want a more exact age, to the second, then don't use INT.
@@599CD Thank you. Yes that makes sense. Only problem I got now is when I in the properties field set the decimal to ie. 2 it still show lots of decimals.
ROUND it. ruclips.net/video/_4yGi65F1GQ/видео.html
Thanks Kenneth, I used your calculation. I liked yours better. I tried his way but it didn't work for some reason. Thanks again!
hpw can i show my age like 41.9 calculated from my birthday?
599cd.com/ask
Please how do i generate query for birthday within a week
599cd.com/Birthday
I need to calculate a persons age by years, months, and days. How do I do that?
See 599cd.com/a?26228
Thanks
Welcome
my end result comes out as age is 19.25466826 or 18.459255 how do i set it so it rounds off
599cd.com/Round
Hi! How to show Age without comma? SELECT Studenti.*, (DateDiff("m",[Data nasterii],Date()))/12 AS Age
FROM Studenti; . This is my query.
599cd.com/Ask
hello sir, i am from India. i am watched maximum videos of yours. now i am facing a little trouble in MS access the problem is how to insert Bullet and numbering in MS Access
Access doesn't have bullets and numbering like Word does.
@@599CD
Thank you so much for your reply ❣️❣️❣️
it keeps on asking to0 enter Parameter Value for the fields Date1
599cd.com/EnterParameterValue
You are funny. If you fail in IT, you still can be a lawyer, or maketing, or sales, etc. It was too cute when you said "don't use datediff!" on your video title.
I'm not sure if this is a compliment or not? :)
@@599CD It is a compliment.
Just go straight to the topic
If you don't like how I present my videos, then there are plenty of other channels to choose from.
WOW. You are something. Do you feel good to be rude.
Mr. He Zheng is really rude. The video man is doing a favor to us. Should be grateful.
Thank you. Usually I just delete rude comments, but once in a while I feel the need to respond. I like to give a little background and present the question before I just jump right in to technical details. If that's not something you enjoy, go find another channel. No need to post a rude comment like this on someone's video. I'm giving you FREE education! Be a little more appreciative and respectful.
:-)
(:
Sir very immpressive
Thanks a lot