Instead of remembering the formula, don't you think the below logic is simpler. (Today's date - Date of birth)/365 and then drag it across Eg (10th march 2010 - 10th march 2000)/365 = 10.3 - if you remove decimal it will be 10 years. Not trying to challenge you, just asking your views.
SOLUTION: You are getting the error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
SOLUTION: You are getting the error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
I use this formula in a Birthday/Anniversary list for my family. It works great until it encounters a Month/Day that hasn't yet occurred. Then the age is off by 1 year. Any ideas how to fix this?
The formula is DATEDIF(date of birth, TODAY(), "Y"). The TODAY() gives you today's date, so it will be for 2021. You can change TODAY() to any date you like. For example, if you wanted to know how old someone was at the start of 2021, then you can change it to 01/01/2021. Hope that helps. Let me know if you have any questions.
I have not found one teaching the shortcuts of computer... No one!!! Jaan de dena jyada aasaan lagta hai... Interview main 5 line bolna aaj tak koi dhang k nahi sikhate... Jaahilo ki bharti!!!
Which version of Excel are you using. The #NAME error appears when Excel doesn't have the function. It was officially recognised and introduced by Microsoft in Excel 2016. You can use =INT((TODAY()- Date of birth)/365) instead, this will also work. Hope that helps.
Sir if you have this file of excel plz open the sheet and delete B3 cell than see what's happening. It means that if we have a blank cell in Column B the result in Column C will be show "121" so plz make another video to resolve this problem i will be very thankful of you. Thank you
You can use the IF function. The formula is IF(logical_test, value_if_true, [value_if_false]). So in this case use =IF(B3="","",DATEDIF(B3, TODAY(), "Y")). The logical test is B3="", as there is nothing inside the double quotations, it means if B3 is blank then it will output no value otherwise output the age. Hope that helps.
AMAZING AND THANK YOU. Tried something extremely similar to this but I was just having a syntax issue. I didnt know the Datedif() function and was trying a different function with the same logic. You are a gentleman and a scholar!
Sir agar kisi ka age only year me diya hai to usko dd/mm/yy form me kaise convert karenge? Jaise kisi ka birth year 1991 me ho to usko 01/01/1991 me convert karne ke liye kaun sa formula lagana padega? '
That's great. So easy when you're shown how. The only question I have is, how do I calculate the age without it changing with time. I would like to keep the age of the person when I received the application, for example. Would I remove the Today reference? Thank you for any help! Your instructions are so clear and straightforward.
This tip was great. I am pleased that I can do it in Excel. I have seen that it can be accomplished in Access, but I want to use Excel more. I love your tip very much.
@@sovannsom2643 Access and Excel are very fine programs. They both have unique powers to perform some heavy-duty jobs with ease. Thank you for writing to me.
SOLUTION: You are getting the value error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
For me, the best tip is here is tell you that you must format birthdate column as date, and age column as NUMBER. No guides I searched for told me this!
Instead of remembering the formula, don't you think the below logic is simpler.
(Today's date - Date of birth)/365 and then drag it across
Eg (10th march 2010 - 10th march 2000)/365 = 10.3 - if you remove decimal it will be 10 years.
Not trying to challenge you, just asking your views.
Yes, I was using it e.g. =ROUND((TODAY()-F7)/365,0) where DOB is written in F7 cell
@@anveshjadav Another solution is =INT((TODAY()- Date of birth)/365)
This one worked for me
I got an Error !
This kind of things that no one serched for but every one need
Mc
Thanks a lot, saved me from lot of stress and time
doesnt work for me, it says "value!"
Why
I had the same error so I changed the formatting in the "Age" column from number to general and it worked.
@@19761999 in my case it did not solved the problem, still VALUE error appears
Use now instead of today and date format should be match with ur system date and time
SOLUTION: You are getting the error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
Formula: =datedif(XN,Today(),"Y")
Where X= A/B/C....
N= 1,2,3.....
It is showing '#Value!' instead of age after typing formula in cell.
Same here..did you get any solution for this
@@harinarayanan2252 no
@@sandeepsharma5637 make the date format as 03-06-2021 not in 03.06.2021 ( avoid unnecessary space at the front of date)
SOLUTION: You are getting the error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
@@ExcelAtData apki formula se bhi results nenhi araha
It does not work
I use this formula in a Birthday/Anniversary list for my family. It works great until it encounters a Month/Day that hasn't yet occurred. Then the age is off by 1 year. Any ideas how to fix this?
Intro sound name bta do sir please
Super Sir
Sir I Want Excel practice files for every video
Can you upload the Excel files next time because it's very useful for everyone
Thankyou
J P Kamalanathan, thank you for your feedback and also for your great tip. :)
Sir your video is fantastic but please tell me intro sound name
the formula doesn't work if the year used is 1800, 1700, etc.
Works only for 1900 and 2000
Excel uses the number of days since 1 January, 1900 for dates. Most people born before then are dead.
@@snuffy467 LOL!!!!
my date of birth is 30 april 1999 what is my age now?
How about if I want to calculate their age for the year 2021? Pls help me thanks.
The formula is DATEDIF(date of birth, TODAY(), "Y"). The TODAY() gives you today's date, so it will be for 2021. You can change TODAY() to any date you like. For example, if you wanted to know how old someone was at the start of 2021, then you can change it to 01/01/2021. Hope that helps. Let me know if you have any questions.
Age 37 this is Jason will be 38
Thank you man, you have helped resolve a life long pending issue of mine. Thanx again!!!
Somebody help me why am I not getting the answer for the below
30-07-1990
=DATEDIF(F254,NOW(),"y")
Not showing the date why? it is supposed to be 31
Don't type NOW
Type Today.
Much better and detailed than anything else I have found.
Intro sound name
Intro sound name
It's great 🎉🎉🎉
I've just discovered this site and am very impressed. Thank you so much!!
Wao, nice
HOW CAN I AVOID ZERO IN AGE CELL, IF DATE OF BIRTH IS NOT ENTERED
Use if(cell="","",age)
Doesn't work
How old am i if i born in 2004??
Depending on the date of birth, either 16 or 17 in 2021.
Intro sound kaun sa hai please reply
Please reply
Thank you so much.
It was a big lesson
for me to learn this
age calculating formula.
Thank you, very simple and clear explanation
I have not found one teaching the shortcuts of computer... No one!!!
Jaan de dena jyada aasaan lagta hai... Interview main 5 line bolna aaj tak koi dhang k nahi sikhate... Jaahilo ki bharti!!!
Funtion not working sir, please solve, my exel is 2007
Manjunath, get the most from Office with Microsoft 365: www.easyclickacademy.com/buy-microsoft-office/
Not working..15/2/2002 please calculate this for me... it's not working
Thanks for sharing dear, much appreciated and really helpful method.
My first time and i immediately subscribed! Very helpful videos. Thank you so much🙂
1986😖😖😖✌
it doesn't work on mac book pro
Thank You very much
You explain very nicely
Doesnt work it says #NAME
Which version of Excel are you using. The #NAME error appears when Excel doesn't have the function. It was officially recognised and introduced by Microsoft in Excel 2016. You can use =INT((TODAY()- Date of birth)/365) instead, this will also work. Hope that helps.
It is really very amazing 🤗
Your explain very clearly but it is not working in Windows 7
Thank you, Duvvuri :) Are you using Microsoft 365? Get it here: www.easyclickacademy.com/buy-microsoft-office/
I have Excel 2021 but there's no DATEDIF function
Excellent. Simple straightforward. You sorted me out!! Blessings!!
It's doesn't work properly in my excel
Kishore do you use Microsoft 365?
Get the most from Office with Microsoft 365: www.easyclickacademy.com/buy-microsoft-office/
big help to me..thanks
Saved me literally hours of tedious calculation. Thanks for providing this resource!
But excel not Perfered this formula due error expect at time of applying Excell pop-up will warn you
Sir if you have this file of excel plz open the sheet and delete B3 cell than see what's happening. It means that if we have a blank cell in Column B the result in Column C will be show "121" so plz make another video to resolve this problem i will be very thankful of you.
Thank you
You can use the IF function. The formula is IF(logical_test, value_if_true, [value_if_false]). So in this case use =IF(B3="","",DATEDIF(B3, TODAY(), "Y")). The logical test is B3="", as there is nothing inside the double quotations, it means if B3 is blank then it will output no value otherwise output the age. Hope that helps.
@@ExcelAtData ok thank you so much
1984
what happens if another year or two pass after my calculation today, is the whole data going to update itself or I should update it from time to time
When I say my age is 22 years 2 months, what does that mean?
My 22 has started or my 23 has started?
AMAZING AND THANK YOU. Tried something extremely similar to this but I was just having a syntax issue. I didnt know the Datedif() function and was trying a different function with the same logic. You are a gentleman and a scholar!
I just used my birth year and the current year and subtract years the old 🗝️ fashion way
Why doesn't my excel have this datedif formula?
If I have the age 22Y 11M 24D can I convert it to birth date?
Sir agar kisi ka age only year me diya hai to usko dd/mm/yy form me kaise convert karenge? Jaise kisi ka birth year 1991 me ho to usko 01/01/1991 me convert karne ke liye kaun sa formula lagana padega? '
TOO GOOD ..
tHANK YOU
You’re Welcome :)
Thank you so much 👍🏻👏🏻👏🏻👏🏻👏🏻
THANKYOU VERY MUCH!!!!
Can anyone tell me how to do the other way around. If I put age, how to show Date Of Birth?
That's great. So easy when you're shown how. The only question I have is, how do I calculate the age without it changing with time. I would like to keep the age of the person when I received the application, for example. Would I remove the Today reference? Thank you for any help! Your instructions are so clear and straightforward.
Good job
Oh my gosh, Works beautiful. Saves so mudh time..Thank you!!💖💖
Thanks sir.
Wonderful much needed.
Can you explain how to hide excel formula.
Great....thanks for it, but why is that I don't have the datedif function enabled on my excel, any solutions
This tip was great. I am pleased that I can do it in Excel. I have seen that it can be accomplished in Access, but I want to use Excel more. I love your tip very much.
@@sovannsom2643 Access and Excel are very fine programs. They both have unique powers to perform some heavy-duty jobs with ease. Thank you for writing to me.
superb
tried it,dosen't work all I get in cell is value
Hi.. how to calculate no.of.days excluding Saturday and Sunday? If we use the same formula
Nice Excellence To Work Amazing teaching way thank you so much
Do you any idea?????
How to find birth year month date Sri lankan nic number.
great
kalau lahir 18 03 1966 berapa umur nya
Hi admin you should upload to short so it's more interesting ❤
just came here to learn Date Calculate mikasa's birth
Thank you so much. Explanation was wonderful and was able to do that.
but what if the other way around. the age is given and how to determine the birthdate
Is it work in ms excel 2007
But in my computer it didn't work your formulae
This is what i like and dislike a whole vedio for 2 hours with no meaning
Very nice explanation Sir. God bless you. Love from India.
sandeep sharma same problem
SOLUTION: You are getting the value error as the date is not in a date format. It is probably in a text format. You can change the cell format by right clicking > select Format Cells > select Date. If this doesn't work try using the formula DATEVALUE. i.e DATEDIF(DATEVALUE (date of birth), DATEVALUE(TODAY()), "Y"). Hope that helps.
Thanks 👍 I got it as you did ,,,, I found it helpful 🙏🙏🙏
what if I have age but need date of birth. How to calculate that?
current date month year - date of birth will also give solution
AWESOME! Love the concise and how articulate the explanation is. Thank you! Made my work easier!
plzz excel name which version??
Could you guide me, if I have a worksheet to store all the events by date. How can I just display today's events in a separate worksheet?
Try searching about filters and advanced filters.
Not working .result comes value
Will this automatically update the age each year?
THANK YOU SO MUCH NOBODY TOLD ME ABOUT THE’ FORMAT CELL’ option
People born in 1987 are 41 years ?
No datedif...how to add this in my function?
Hey... But in my case I will do in same ...
For me, the best tip is here is tell you that you must format birthdate column as date, and age column as NUMBER. No guides I searched for told me this!
GREAT !!! HELLO ITS REALLY WORKS !!! GOOD HAVE A BEAUTIFUL FUTURE :-)
Bhai tera work, mere exel main work Q ni kr raha🤣
Thank u very much
Would you give us its Cod ???
I TRY ALL ITS STILL SHOWING VALUE AND NAME