Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
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
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
=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]
|
Jimbothebarbarian
Member
Registered: 19th Apr 07
Location: Cumbria..........drunk..
User status: Offline
|
Walks in reads thread, walks out dumbfounded.....
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
it just says #value
I wonder, what format does the DOB need to be in? atm its dd/mm/yy
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Is your DOB stored as a date or text?
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
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]
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
that's only the case for years after 99 though <00 works fine
[Edited on 16-12-2014 by Steve]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
I can't really help without seeing your input data.
Here's it working:
http://cl.ly/2S0V0n393k1o/Book1.xlsx
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
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
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
Similarly if you input 2000 as the DOB it produces
4AZhu
needs to be
04AZhu
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Ah, I see.
Try this:
=RIGHT(YEAR(C1)+(IF(MONTH(C1)<9,4,5)),2)&LEFT(A1,1)&LEFT(B1,4)
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Leading zero should be sorted too
[Edited on 16-12-2014 by evilrob]
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
Awesome
I knew there must be a simpler way of doing it, thanks
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
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?
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
No probs. ![](/board/images/smile/cool.gif)
I'm sure Dom will be along with a formula that pisses all over my 62 character effort.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
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?
If the month is less than 9 (i.e. September) add 4 years to arrive at YOE, otherwise add 5.
|
nibnob21
Premium Member
Registered: 16th May 10
Location: South Derbyshire
User status: Offline
|
God I dislike Excel.
Get yourself MATLAB.
MX5 Project Thread
|
Ojc
Member
Registered: 14th Nov 00
Location: Reading: Drives : Clio 197
User status: Offline
|
I used to do this
|