How to find age of a birthday

=DATEDIF(TEXT(LEFT(LEFT(C6,6),2)&"-"&MID(LEFT(C6,6),3,2)&"-"&RIGHT(LEFT(C6,6),2),"dd-mm-yy"),TODAY(),"Y")&" Years"

this should work, assuming 1210663455 is the standard format of the data from which you need to find the age, ie 6 characters of date, followed by 4 other numbers.

https://en.excel-translator.de/translator/

Please Login or Register  to view this content.

to walk you through, i did this by combining 4 steps into one.

1. extracting date from 1210663455
2. inserting - to make the text look "dd-mm-yy"
3. Finding difference of date from today's date
4. adding "years" into the result (completely optional)

1. to extract the date, i simply used LEFT function, since the date is the left 6 charactes from 1210663455
2. to add in the "-" between the numbers, i used left, mid & right functions along with "&" function
3. to find the difference between dates, i used datedif function
4. to add the "years", i simply put &" years" at the end of the formula

=DATEDIF(LEFT(LEFT(C6,6),2)&"-"&MID(LEFT(C6,6),3,2)&"-"&RIGHT(LEFT(C6,6),2),TODAY(),"Y")&" Years"

you can use this function instead because the text function in the earlier version is unnecessary.

how to work on the same with a different language is unknown to me.

ah.. nice thanks :-)
But I do understand english

But I dont understand "code" :-)

Should I place 1210663455 in C6 and run this code in ex. C7 or any other cell and get result there?

Is there a way to calculate from a selected date and not today?

you can place 1210663455 in c6 and run the formula (code) in any other cell, you will get the result there.

you can replace c6 in the formula (code) with your desired cell number (where your actual data is) and it will still work.

To calculate age from a selected date, you can simply replace the "TODAY()" with cell reference of the selected date.
example: if you want to calculate age of 1210663455 with 01/01/2021 entered in cell b7, you can replace "today()" with "b7" and get the desired result.

hope this helps

how to work on the same with a different language is unknown to me.

I was not mentioning your english skills here. the formula/code varies with MS Office Language. like Bsalv has mentioned in his formula, "," wont work with other language setup excels. instead you may have to use ";"

my knowledge on this matter is limited.

as you can clearly see in your screen snap, C8 holds only "=A1" and not the code.

also, you have to replace the "today()" entirely with cell reference.

in the formula you have mentioned a1(). that is unnecessary and will result in an error.. instead use this:

Formula:

Please Login or Register  to view this content.

i have attached a sheet with the formula. ill be going offline soon and hope the sheet attached will help you with your doubts.

You're welcome.

Yes you can use conditional formatting to do that. that would not be a part of the formula/code though.

select the area where the age will appear (in this case, the entire column), go to conditional formatting, highlight cell rules, more rules, (refer the screensnip), enter the value, select the desired format, click ok.

If that takes care of your original query, please mark this as solved. you might not be aware of this but you can actually thank those who took effort to help with your query by clicking on the star (add reputation) in the bottom left corner of the post and comment on the level of help you got from the user.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Try this:

=YEAR(TODAY())-YEAR(DATE(MID(A1,5,2),MID(A1,3,2),LEFT(A1,2)))=65

AliGW on MS365 Insider (Windows) 64 bit

	A	B
1	1210663455	FALSE
2	1210563477	TRUE

Sheet: Sheet2

Attach a sample workbook as requested in post #17.

Did you try my suggestion?

yes..
create new sheet

a1= 1210663455
a2= 1210563477

b1 and b2 your code... but gives error

Capture.PNG

You need to change commas to semi-colons for your Danish locale:

=ÅR(IDAG())-ÅR(DATO(MIDT(A1;5;2);MIDT(A1;3;2);VENSTRE(A1;2)))=65

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Is this what you really wanted?

=YEAR(TODAY())-YEAR(DATE(MID(A1,5,2),MID(A1,3,2),LEFT(A1,2)))>=65

Had you said that in post #1, we'd have reached a conclusion very much faster!

Glad to help.

thanks again again :-)

in #16 I tried to explain it... but maybe it's to misunderstand :-)

In post #16 you said this:

I just need to find who is having 65 birthday in 2021

That's not actually what you wanted, but it is what you asked for and what I gave you. That's why there was only ONE result: there is only one person on the list who turns 65 in 2021.

Hi AliGW

just one little problem :-)

When born in 2000 and forwards they get TRUE...

when born in 2000 and forwards they get true...

OK - try this:

=YEAR(TODAY())-(IF(MID(A1,5,2)<=RIGHT(YEAR(TODAY()),2),20,19)&MID(A1,5,2))>=65

=ÅR(IDAG())-(HVIS(MIDT(A1;5;2)<=HØJRE(ÅR(IDAG());2);20;19)&MIDT(A1;5;2))>=65

Next time you ask a question, provide a set of sample data that includes ALL eventualities. This will save you a lot of time and prevent your helpers going round in circles.

Thanks again

If I myself was aware of this issue I would have told you.. :-)

I appriciate the time you use on this. It helps me a lot.


Is there a way to change this on all my sheets, so that I dont have to do it manually?

To change the code on all sheets column B and further more the conditional formatting also on column B

I did it on the first sheet and maybe there is a way to copy this to the 20 other sheets, without deleting A and B column content.

If I myself was aware of this issue I would have told you.. :-)

Oh, so you didn't know you'd have dates after 1999 in the list? Fair enough.

I'm not clear on the problem you are having copying the formula from one sheet to another. If the layout is the same, just copy the formula from the address bar, go to the next sheet and paste it into C1, then drag copy down. It would take no more than a couple of minutes to do this for 20 sheets. Maybe I haven't understood the issue.

As for the conditional formatting, you will probably have to set this up for each sheet, however it's one rule and can be applied to a range.

If you want to share a more comprehensive sample workbook that demonstrates the issues you think you have, I can take a look. Without one, I am simply guessing.

Glad I was able to help, anyway. We got there in the end!