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

JustJoe

Wow, That was fast, Thanks > your help is very much appreciated.

 

J. Craig Williams

I'm glad I could help - the problem I am working on is driving me nuts and shifting gears for a few minutes helps me from going completely crazy.

Craig

Paul Newcome

Joe: Craig is the Yoda here. I'm surprised it took him that long. Haha

 

Craig: At least I'm not the only one that has the problem of needing to shift gears sometimes. Haha. There have been plenty of times I've jumped on here to take a break from whatever wall I'm beating my head against.

 

This solution actually helped me with something else I was working on, so I'm glad I stopped by. 

J. Craig Williams

Glad it was timely, Paul.

(oversharing deleted)

Craig