write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Wednesday, 16 November 2011

Find Financial Year- with Formula and Vba

To find out financial year from given use below formula
Assuming your date in cell B3




To increase/decrease days, months, years
Assuming your date in cell A11 you can increase year by 1

To increase/decrease months
Assuming your date in cell A10

This will increase month by 12 from given date

With help of below VBA code you can also find the Financial year
Assuming your dates are in column A

Sub Mtest()

    With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
       .Formula = "=RIGHT(YEAR(A2)-(MONTH(A2)<4),2)&""-""" _
        & "&RIGHT(YEAR(A2)+(MONTH(A2)>3),2)"
        'use this line if you dont want to keep formula
        '.Value = .Value
    End With
End Sub