## Monday, February 4, 2008

### How to get the Financial Year from a given Date ?

First of all, why do we require this thing ?

Let me clarify it. While importing Vouchers from Excel to Tally, we require a field which contains unique values. Normally, there is such a field (Invoice Number for Sales) which contains unique values. However, Invoice numbers are unique for a given year and the number restarts from 1 every year. This would create problems as the next year vouchers will overwrite the current year vouchers. Hence, we need to generate a unique value which is a combination of the "Invoice Number" and the "Financial Year".

Here's the Excel formula to get the FINANCIAL-YEAR from a given Date.

FORMULA
`=IF(AND(MONTH(\$A#)>=4,MONTH(\$A#)<=12),YEAR(\$A#) & "-" & YEAR(\$A#)+1,YEAR(\$A#)-1 & "-" &YEAR(\$A#))`

Example
`DATE           FINANCIAL YEAR2007-04-01     2007-082008-04-01     2008-09`
Remarks:-
1) To test the above formula, substitute the A# with the Excel-sheet Cell-reference (say A1).
2) To use this formula in UDI-Magic XML tags, you need to replace the special characters with their ESCAPE SEQUENCES.