Sunday, February 3, 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 YEAR
2007-04-01 2007-08
2008-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.

No comments: