Excel 2016 calculate fiscal year

Question

Fiscal Year starts July 1 - June 30, I need to calculate and display the number of Fiscal Years between 2 dates.

For example: 01/01/2018-12/31/2018. Result (YYYY-YYYY) should give 2 fiscal years:

2017-2018 (July 1, 2017 - June 30, 2018)
2018-2019 (July 1, 2018 - June 30, 2019)

Its easy to calculate if there are only 2 fiscal years between 2 dates but using =IF(StartDate<DATE(YEAR(StartDate), 7,1),CONCATENATE(YEAR(StartDate)-1, "-", YEAR(StartDate)), CONCATENATE(YEAR(StartDate), "-", YEAR(StartDate)+1)) or =IF(EndDate<DATE(YEAR(EndDate), 7,1),CONCATENATE(YEAR(EndDate)-1, "-", YEAR(EndDate)), CONCATENATE(YEAR(EndDate), "-", YEAR(EndDate)+1))

But how do I capture if there are more than 2 fiscal years? For example: 01/01/2018 - 09/20/2019

I would prefer the fiscal year be entered in a table range and automatically insert a new row if there are more than 2 fiscal years. I'm looking for either formula or vba solution.

I tend to evaluate against a single date in this situation (my company has the same fiscal year pattern), so any date is assessed as >=2018Jul01 and you then do a subtraction of the final and start fiscal years to determine total number of fiscal years. hopefully that makes sense? i do my work in VBA so i can capture the fiscal years and do the subsequent subtraction... 2016jun01 to 2018Aug01 recognizes first entry in 2016 fiscal year, second entry is 2019 fiscal year, so it's 3 fiscal years total. — Cyril

Cyril Cyril · Accepted Answer · 2018-08-21T20:46:40

last time i had to do this was for a specific report where the dates are written in cells:

dim s as long, e as long
if month(cells(1,1).value) >= 7 AND day(cells(1,1).value) >= 1 Then
    s = year(cells(1,1).value)+1
else
    s = year(cells(1,1).value)
end if
if month(cells(2,1).value) >= 7 AND day(cells(1,1).value) >= 1 Then
    e = year(cells(1,1).value)+1
else
    e = year(cells(1,1).value)
end if
'FY range:
cells(3,1).value = "FY" & s & "-FY" & e

rehashed the code for you, untested

Excel 2016 calculate fiscal year

1 Answers