Counting overlapping days between two time periods.
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!
Comments
-
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
-
Wow, That was fast, Thanks > your help is very much appreciated.
-
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
-
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.
-
Glad it was timely, Paul.
(oversharing deleted)
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives