# Thread: Formula to display year based on the financial year

1. ## Formula to display year based on the financial year

Hi Excel Experts,

Our financial year is Oct-Sept. I have been consolidating and reporting data based on this year on year. Now I would like to have a seperate worksheet that I could use to do a quick comparison each year. What is the best way of doing this without merging the three files.

I would like to just have the report and graphs.

One of the immediate formula i am looking is : for Oct-09 to Sept-10 i would like to have a formula that will display year as 2009-2010
So that I could use the Month and use a drop down for the years 2009-2010, 2010-2011 and 2011-2012.

Can someone help me with a formula for this.

Regards
Baiju

2. Baiju,

This code will generate an array that you can use to generate the list for your dropdown.
Code:
```Option Explicit

Public zFYs(20) As String   '*** Use this array to initialize your dropdown ***

Sub CalcFYDates()

Dim dteMinDate  As Date
Dim dteMaxDate  As Date
Dim iFirstFY    As Integer
Dim iLastFY     As Integer
Dim iCntr       As Integer

dteMinDate = WorksheetFunction.Min(Range("FYDates"))
dteMaxDate = WorksheetFunction.Max(Range("FYDates"))

iFirstFY = IIf(Month(dteMinDate) < 10, Year(dteMinDate) - 1, Year(dteMinDate))
iLastFY = IIf(Month(dteMaxDate) > 9, Year(dteMaxDate) + 1, Year(dteMaxDate))

For iCntr = iFirstFY To iLastFY - 1
zFYs(iCntr - iFirstFY) = Format(iCntr, "####") & "-" & Format(iCntr + 1, "####")
'      Debug.Print zFYs(iCntr - iFirstFY)
Next iCntr

'   MsgBox "Mim Date: " & Format(dteMinDate, "mm/dd/yy") & vbCrLf & _
'          "Max Date: " & Format(dteMaxDate, "mm/dd/yy") & vbCrLf & _
'          "Start First FY: " & iFirstFY & vbCrLf & _
'          "End   Last FY: " & iLastFY, _
'          vbOKOnly + vbInformation, "Date Range"

End Sub    '*** CalcFYDates ***```
You can uncommet the Debug & MsgBox code for debugging and delete them when you are satisfied that it works.

3. If the date is in A1, you could use a formula like:
=YEAR(A1)-(MONTH(A1)<10)&"-"&(YEAR(A1)+(MONTH(A1)>9))

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•