Counting overlapping days between two time periods.

JustJoe

Hello,

 Our companies fiscal year does not match the calendar year, our fiscal year starts April 1st and ends March 31st.

 I'm looking for a clean way to count overlapping days.

 Task Start = 4-15-18 / Task End = 6-20-20.

 Fiscal Year 1 = 4-1-18 to 3-31-2019

 Fiscal Year 2 = 4-1-19 to 3-31-20

 How many Task days are in Fiscal Year 1, how many are in Fiscal Year 2?

Any help finding a clean way to do this would be greatly appreciated!

J. Craig Williams

For each FY, you need somewhere to store the start and end dates (it makes it cleaner). I denote those with FYstartref and FYendref - replace them with valid references)

The formula will be something like this:

=NETDAYS(MAX([Task Start]@row, FYstartref), MIN([Task End]@row, FYendref))

This returns

FY18 - 351

FY19 - 366

FY20 - 81

I hope this helps.

Craig