|
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 » Post Reply
|
Ojc |
posted on 29th Dec 14 at 21:59 |
I used to do this | |
nibnob21 |
posted on 16th Dec 14 at 18:07 |
God I dislike Excel. | |
evilrob |
posted on 16th Dec 14 at 14:50 |
quote: If the month is less than 9 (i.e. September) add 4 years to arrive at YOE, otherwise add 5. | |
evilrob |
posted on 16th Dec 14 at 14:50 |
No probs. :cool: | |
Steve |
posted on 16th Dec 14 at 14:42 |
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? | |
Steve |
posted on 16th Dec 14 at 14:36 |
Awesome :thumbs: | |
evilrob |
posted on 16th Dec 14 at 14:32 |
Leading zero should be sorted too | |
evilrob |
posted on 16th Dec 14 at 14:32 |
Ah, I see. | |
Steve |
posted on 16th Dec 14 at 14:31 |
Similarly if you input 2000 as the DOB it produces | |
Steve |
posted on 16th Dec 14 at 14:27 |
| |
evilrob |
posted on 16th Dec 14 at 14:26 |
I can't really help without seeing your input data. | |
Steve |
posted on 16th Dec 14 at 14:26 |
that's only the case for years after 99 though <00 works fine | |
Steve |
posted on 16th Dec 14 at 14:24 |
ok its sort of working now, but its sticking a 1 infront of the year so its outputting | |
evilrob |
posted on 16th Dec 14 at 14:20 |
Is your DOB stored as a date or text? | |
Steve |
posted on 16th Dec 14 at 14:19 |
it just says #value | |
Jimbothebarbarian |
posted on 16th Dec 14 at 14:16 |
Walks in reads thread, walks out dumbfounded..... | |
evilrob |
posted on 16th Dec 14 at 13:57 |
=RIGHT(YEAR(C1),2)+(IF(MONTH(C1)<9,4,5))&LEFT(A1,1)&LEFT(B1,4) | |
Steve |
posted on 16th Dec 14 at 13:48 |
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 |