I used to do this

God I dislike Excel.

Get yourself MATLAB.

quote:

---

Originally posted by Steve
don't understand the <9,4,5 bit though, its to do with checking if before sept the 1st but how do those numbers equal that?

---

No probs. :cool:

I'm sure Dom will be along with a formula that pisses all over my 62 character effort.

don't understand the <9,4,5 bit though, its to do with checking if before sept the 1st but how do those numbers equal that?

Awesome :thumbs:

I knew there must be a simpler way of doing it, thanks :)

Leading zero should be sorted too

[Edited on 16-12-2014 by evilrob]

Ah, I see.

Try this:
=RIGHT(YEAR(C1)+(IF(MONTH(C1)<9,4,5)),2)&LEFT(A1,1)&LEFT(B1,4)

Similarly if you input 2000 as the DOB it produces

4AZhu

needs to be

04AZhu

Data input is the same, and works fine in mine too with your example, its if the years fall between 00 and 10

try it on yours and you will see

I can't really help without seeing your input data.

Here's it working:
http://cl.ly/2S0V0n393k1o/Book1.xlsx

that's only the case for years after 99 though <00 works fine

[Edited on 16-12-2014 by Steve]

ok its sort of working now, but its sticking a 1 infront of the year so its outputting

100AZhup
should be
00AZhup

[Edited on 16-12-2014 by Steve]

Is your DOB stored as a date or text?

it just says #value

I wonder, what format does the DOB need to be in? atm its dd/mm/yy

Walks in reads thread, walks out dumbfounded.....

=RIGHT(YEAR(C1),2)+(IF(MONTH(C1)<9,4,5))&LEFT(A1,1)&LEFT(B1,4)

A1 = First name
B1 = Last name
C1 = DOB

[Edited on 16-12-2014 by evilrob]

so I managed to create a formula to work out a childs intake year in yy format based on their full DOB, by breaking down each step and adding it to the formula bit by bit

The complication is because year of entry is from sept to sept, someone born in the same year could fall under a different YOE if they were born before or after sept 1st.

The formula also takes the first initial and last 4 characters of the surname and appends the DOB, so for instance would create 01SWill.

Here is what I have

=TEXT(DATE(YEAR(C722),MONTH(C722)+48+IF(AND(C722>DATE(TEXT(C722,"yyyy"),8,31),C722<DATE(YEAR(DATE(TEXT(C722,"yyyy"),8,31)),MONTH(DATE(TEXT(C722,"yyyy"),8,31))+12,DAY(DATE(TEXT(C722,"yyyy"),8,31)))),"12", "0"),DAY(C722)),"yy")&LEFT(A722,1)&LEFT(B722,4)

it works perfectly, but it looks over complicated, anything I can do with that to shorten it? for no other reason than if anyones bored and fancies a challenge :lol: