corsasport.co.uk
  Not logged in [Login - Register]  
You Are Not Registered Or Not Logged In  

Corsa Sport » Message Board » Off Day » Geek Day » Can anyone simplify this Excel formula


New Topic
New Poll 		  Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author Can anyone simplify this Excel formula
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 13:48   View Garage View User's Profile U2U Member Reply With Quote
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

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 13:57   View Garage View User's Profile U2U Member Reply With Quote
=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
16th Dec 14 at 14:16   View User's Profile U2U Member Reply With Quote
Walks in reads thread, walks out dumbfounded.....
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:19   View Garage View User's Profile U2U Member Reply With Quote
it just says #value

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

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:20   View Garage View User's Profile U2U Member Reply With Quote
Is your DOB stored as a date or text?
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:24   View Garage View User's Profile U2U Member Reply With Quote
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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:26   View Garage View User's Profile U2U Member Reply With Quote
that's only the case for years after 99 though <00 works fine

[Edited on 16-12-2014 by Steve]
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:26   View Garage View User's Profile U2U Member Reply With Quote
I can't really help without seeing your input data.

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:27   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:31   View Garage View User's Profile U2U Member Reply With Quote
Similarly if you input 2000 as the DOB it produces

4AZhu

needs to be

04AZhu
evilrob
Premium Member

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:32   View Garage View User's Profile U2U Member Reply With Quote
Ah, I see.

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

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:32   View Garage View User's Profile U2U Member Reply With Quote
Leading zero should be sorted too

[Edited on 16-12-2014 by evilrob]
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:36   View Garage View User's Profile U2U Member Reply With Quote
Awesome

I knew there must be a simpler way of doing it, thanks
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
16th Dec 14 at 14:42   View Garage View User's Profile U2U Member Reply With Quote
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

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:50   View Garage View User's Profile U2U Member Reply With Quote
No probs.

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

Avatar

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
16th Dec 14 at 14:50   View Garage View User's Profile U2U Member Reply With Quote
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

Avatar

Registered: 16th May 10
Location: South Derbyshire
User status: Offline
16th Dec 14 at 18:07   View Garage View User's Profile U2U Member Reply With Quote
God I dislike Excel.

Get yourself MATLAB.

MX5 Project Thread
Ojc
Member
Registered: 14th Nov 00
Location: Reading: Drives : Clio 197
User status: Offline
29th Dec 14 at 21:59   View User's Profile U2U Member Reply With Quote
I used to do this

 
New Topic
New Poll

  Related Threads Author Forum Replies Views Last Post
Excel Help Darren General Chat 3 647
4th Mar 04 at 11:51
by ipswichcorsa		  
Excel Formula Help.. Bart Geek Day 6 709
7th Dec 06 at 07:59
by Bart		  
Working out 15% (VAT) in Excel. deano87 Geek Day 24 776
10th Feb 09 at 19:25
by deano87		  
Help with Excel and todays date? A2H GO Geek Day 7 379
6th Nov 09 at 17:06
by Ojc		  
Excel Formula RCS Geek Day 1 353
6th Jul 12 at 13:39
by Ian		  

Corsa Sport » Message Board » Off Day » Geek Day » Can anyone simplify this Excel formula 30 database queries in 0.0125811 seconds