Excel Date of birth calculation from nic no

i want to calculate date of birth from that nic no, so this is the rules
the first two characters show their birth year,
the mid value(mid value means the 3rd, 4th and 5th character in that nic no) shows their date and month.
that means
january 31
february 29
march 31
april 30..........etc (going through our normal calender, only February has 29 days for calculation like leap year calculation)

eg:- some one born in 1995.03.15
then their first two character of nic no is 95 and the next three character calculate with their birth month and day, that means
1995.06.15
six months
january, february, march, april, may, june
31+29+31+30+31+15=136
now the first five nic no is 95136

if this is male 136 (nic no = 95136)

if this is female 136+500=636 (nic no = 95636)

that means if that date of birth is for female then we add 500 with our normal calculation,
so how to make a formula for this one in excel.
if we want to add more than one column tell me what to do..

also please help the other columns age and day of birth...


anyways thanks in advance..

Here's a fun formula:

Formula:

=(("19"&LEFT(A2,2))&"-"&IF(MOD(("19"&LEFT(A2,2))*1,4)=0,IF(B2>500,MONTH(B2-500),MONTH(B2))&"-"&IF(B2>500,DAY(B2-500),DAY(B2-1)),IF(B2>500,MONTH(B2-500),MONTH(B2))&"-"&IF(B2>500,DAY(B2-500),DAY(B2))))*1

Note: Your addition in the 1st post is incorrect... day 136 is in May, not June.

Try it and let me know.

- Moo

* Edit: While it works (for years prior to 2000), I'm glad I only spent about 15 minutes on that beast of a formula... daddylonglegs' solution is SO MUCH EASIER!

oh yaa
thats my mistake

thanks for your great support.....

january, february, march, april, may, june
31+29+31+30+31+15=136

Your addition in the quote above is incorrect. --> 31+29+31+30+31+15 = 167, not 136.

The 136th day of the year (in a leap year) is May 15th, not June 15th.
Jan+Feb+Mar+Apr ---> 31 + 29 + 31 + 30 = 121 with the remaining 15 days coming in May.

- Moo

Just using the data in column A this formula will give you DOB assuming the correct number of days in February

=DATE(LEFT(A2,2),1,MOD(MID(A2,3,3),500))

(assumes DOB will be before 2000, but can be modified to cope with later dates)

If you need to assume that February always has 29 days then try this version

=EDATE(DATE(0,1,MOD(MID(A2,3,3),500)),LEFT(A2,2)*12)

Edit: and for Age and Day of birth you can base that on the above so if DOB is in D2 use this formula in E2

=DATEDIF(D2,TODAY(),"y")

and in F2

=TEXT(D2,"dddd")

Age: =DATEDIF(D2,TODAY(),"Y")
Example results should be 17, 17 and 21

- Moo

thanks for your great help..

Is 95367 valid, why isn't that 96001?

Did you try the second formula I suggested?

thank you...

Originally Posted by Dilshanth10

if this is male 136 (nic no = 95136)

if this is female 136+500=636 (nic no = 95636)

That's from your original post - from that I assume that the numbers will either be in the range 1 to 366 (male) or 501 to 866 (female). Is that incorrect? Making that assumption you shouldn't have a 367 should you?

your formula is correct.. but b2>366 is change to b2>500

Note that I was talking about the formula that I suggested (not Moo's formula), that doesn't use b2>366......

I would highly recommend using DADDYLONGLEGS' formula from Post #6 above.

While my formula, with a few tweaks, can work fine - it is not the best way to solve your original problem.

- Moo

i see your formula once again... and you r correct friend... its my mistake... sry for that...
your formula is 100% correct....
but when u type 925001234v and it shows the date of birth 1992-1-0 ...
do you have any idea to stop that calculation for 925001234v nic no...


anyway thanks again...

Originally Posted by srktech

Can you share me the excel document?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Could you please share the formula for the new NIC number?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

See: https://srilanka-information-zone.bl...-card-nic.html