Convert year to its financial year [Solved] ~ Fix Your PC Errors & Speed Up Your PC

3/04/2016

Convert year to its financial year [Solved]

Convert year to its financial year

Dear solver,
From cell M6 of a sheet, I have enrolment number of student(s), is it generated by a formula as follows:
=IF(C6="","",IF(T6="","BRANCH NAME MISSING",IF(O6="","PLEASE ENTER DOA",VLOOKUP(T6,Branch_Code!$B$6:$C$8,2,FALSE)&"/"&TEXT(SUMPRODUCT((--(TEXT($O$6:O6,"mmm/yyyy")=TEXT(O6,"mmm/yyyy"))*($T$6:T6=T6))),"00")&"/"&TEXT(O6,"mmm/yyyy"))))

C6 contains = Student's name, T6 contains = Branch name, O6 contains = Date of Admission, and then the main formula start ie vlookup....
And the output comes as :
01/01/May/2006 where,
01 = Branch Code, 01 = Nth numberof student, May = Month of Admission, 2006 = Year of admission.

Now I want, In cell L6, a registraion number should generate based on M6 i.e.
enrolment no.
Registraion number should be as 200607050101 or 2006050101.
Means Financial year(200607) or Only year(2006)/Month name/Branch code/Nth number of student
but without forward slashes ("/").
My Financial year start on April 01 and ends on 31 March.

I got this formula here at forum :
=IF(C6="","",IF(T6="","BRANCH NAME MISSING",IF(O6="","PLEASE ENTER DOA",VLOOKUP(T6,Branch_Code!$B$6:$C$8,2,FALSE)&TEXT(SUMPRODUCT((--(TEXT(N$6:$O6,"mmm/yyyy")=TEXT(O6,"mmm/yyyy"))*(S$6:$T6=T6))),"00")&TEXT(O6,"mmyyyy"))))

But, this returns "year" at end ie 0101052006, but I want year at the begining, because excel doesnt accepts zero in begining.
i.e.
01 will treat as 1.
Many thanks
Ayushman

Keys to the Problem Convert year to its financial year

Download Error Fixer (Free)

This will put the year in the front
=IF(C6="","",IF(T6="","BRANCH NAME MISSING",IF(O6="","PLEASE ENTER DOA",TEXT(O6,"yyyy")&VLOOKUP(T6,Branch_Code!$B$6:$C$8,2,FALSE)&TEXT(SUMPRODUCT((--(TEXT(N$6:$O6,"mmm/yyyy")=TEXT(O6,"mmm/yyyy"))*(S$6:$T6=T6))),"00")&TEXT(O6,"mm"))))
Result I got - 2012990106
If your basic problem is to show leading zerores then uses Number Format as Custom and use the code
0000000
this will add leading zeroes so that the resulting number is at least 7 digit long.
You may adjust the number of zeroes as you want.

Make Sure that your System Meets the System Requirements

Microsoft Windows Requirements :

1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
16 GB available hard disk space (32-bit) or 20 GB (64-bit)

You need to double-check your computer's hardware configuration against the following Microsoft requirements. If your PC hardware is not up to par, then make sure you update where necessary before continuing.

Recommended Method to Repair the Problem: Convert year to its financial year:

How to Fix Convert year to its financial year with SmartPCFixer?

1. Download Error Fixer. Install it on your system.

2. After the scan is finished, you can see the errors and problems which need to be repaired.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been removed.

Fix Your PC Errors & Speed Up Your PC

This is a blog which will post useful infomation about fixing computer errors. To help people troubleshoot errors, speed up computers and share good apps.

3/04/2016