How do I count days between 2 dates but break them out by month. For example. 9/30-10/1. This is a c
How do I count days between 2 dates but break them out by month. For example. 9/30-10/1. This is a count of 2 days but i want to capture it as 1 day in October and 1 day on September
Answers
-
I do this using 4 IF formulas and add them together.
IF date range starts before 1st and ends before last day of month, calculate days between 1st and end.
IF date range starts after 1st and ends before last day of month, calculate days between start and end.
IF date range starts before 1st and ends after last day of month, calculate days in month.
IF date range starts after 1st and ends after last day of month, calculate days between start and last day of month.
Adding these 4 together will give you the days in the month.
To make it easier you probably want to make the month a reference another cell so that you aren't adding the month in the formula multiple times and can reuse the formula for any month. Or you could set up another sheet where you define the start and end of each period and refer to that.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!