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.


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.
01 will treat as 1.
Many thanks

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
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.

Related: Problem With Desktop System Icons (Win7 Ultimate SP1, 32-bit),My Invoices & Estimates Deluxe 10 Set Trouble,Constant BSOD, SFC Found Problems But Unable To Resolve,Excel Worksheet W/ Drop Downs,Urlmon.dll Not There, But Actually Is,I Have Been Accessing An Excel File Located On My Desktop With My Laptop. Suddenly, I Get A Message Saying This File Cannot Be Opened.,Automatic Changing Cell Colours,I Now Have A Windows Internet Explorer Add-ons Disabled Message,Dosent Show Attachment In Hotmail With Internet Explorer 9 Windows 7,Partition Recovery With Windows 7
Read More: [Solved] tenho windows 7 versão baixada e liberada para teste. Não consigo alterar as propriedades de Administrados, com controle total. Isto impede a gravação de registro de um programa adquirido da PINIWEB. Como faço?,How to Resolve - Computer says i cannot access Microsoft Security Essntials in safe mode,how do i exit safe mode?,[Solution] BS: IRQL-NOT-LESS-OR-EQUAL,Lottery module is not updating.,Error installing OKI B4250 driver for Windows 7,Microsoft.NET Framework 2 or Microsoft.NET Framework 4 Client Profile,issues with Windows 7 Desktop freezing,How to Defragment Disks in Windows 7?,add a hypen in excel field


Post a Comment

Note: Only a member of this blog may post a comment.

Sample Text

Copyright © Fix Your PC Errors & Speed Up Your PC | Powered by Blogger Design by ronangelo | Blogger Theme by