#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Convert Excel formula to Access

## SGT

Good day,

Can anyone please help me to convert the following excel formula to an access formula:

=DATE(LEFT(B1,2)+IF(LEFT(B1,2)<RIGHT(YEAR(TODAY()),2),2000,1900),MID(B1,3,2),MID(B1,5,2))

B1 must be [IdNumber] in access - This is a 13 digit South African Id Number. This Excel formula extracts the date of birth (1975-02-22) from this number: 7502225048088
It also compensates if a person was born in 1900's and 2000's. Any other sugestions would be helpfull.

Above formula works in excel if you put the formula in cell C1 and the Id number in B1 (formatted as text to compensate for leading 00). I need something in access that does the same.

Thank you

SGT

----------


## SGT

ID Number breakdown:

75 = year
02 = month
22 = day
5 = gender

----------


## maw230

All of those functions work in Access except maybe 'Today()'.  Exchange that for 'Date()' and remove the '=' at the beginning and it should work.  Of course replace the cell references with field references. 

It would probably make more sense to store the result of the function in another field in a table vs. calculating it on the fly each time you need it.

----------


## SGT

Thank you for the reply.

I fiddled with it per your suggestion and found that this works: 

=Left([IdNumber],2)+IIf(Left([IdNumber],2)<Right(Year(Date()),2),2000,1900) & "-" & Mid([IdNumber],3,2) & "-" & Mid([IdNumber],5,2)

Original:
=DATE(LEFT(B1,2)+IF(LEFT(B1,2)<RIGHT(YEAR(TODAY()),2),2000,1900),MID(B1,3,2),MID(B1,5,2))


The red was not necessary and i had to concatenate the red commas if that makes sence.

For those who are lost, this formula is to get the Date of Birth (1982-12-25) from the id number 8212250058088 (South African Id Numbers). It is a formula for Access inside a query.

Thanx maw230

----------


## maw230

Glad you got it working!

----------

