Calculate number of days per month within a date range that spans more than one year

I have a schedule that spans (2) calendar years. I want to return the number of days per month included in the range. Since I use this result for billing purposes, I have a column per month and year of the schedule the returns the number of days in that given month/year. I am running into problems once I get to the same month of the following year.

I used the following formula successfully to manage the first 12 months of the schedule; however, when you get to the same month in the following calendar year it fails to recognize the difference between the months of different years:

=IFERROR(VALUE((YEAR([End Date]@row) - YEAR([Start Date]@row)) * IF(AND([End Date]@row - [Start Date]@row < 365, OR(MONTH([End Date]@row) = 8, MONTH([Start Date]@row) = 8)), 0, 31) + IF(AND(OR(MONTH([Start Date]@row) < 8, YEAR([Start Date]@row) < YEAR([End Date]@row)), MONTH([End Date]@row) = 8), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) = 8, MONTH([End Date]@row) = 8), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < 8, MONTH([End Date]@row) > 8), 31, IF(AND(MONTH([Start Date]@row) = 8, OR(MONTH([End Date]@row) > 8, YEAR([End Date]@row) > YEAR([Start Date]@row))), 31 - DAY([Start Date]@row), ""))))), "")

How can this be modified to add in the year so that as it carries across future months of a different year that it recognizes the difference between a certain month that falls in one year or the other? It seems that an additional condition can be placed to capture if the year is within YEAR("0000") but I am having trouble making it work.

MANY THANKS IN ADVANCE!!

Find more posts tagged with

Manufacturing

Construction

Community Platform

Conditional Formatting

Project Management

Accepted answers

All comments

Paul Newcome

Is this the same question you had here?

https://community.smartsheet.com/discussion/comment/391586#Comment_391586

JKL

@Paul Newcome Yes sir!

Paul Newcome

Ok. Then are you able to provide screenshots for context?

JKL

Hopefully I can explain the issue here a little clearer: This formula cannot differentiate specific years. My sheet has a column for every month/year such as Aug-23, Sept-23, Cot-23, Nov-23, Dec-23, Jan-24 and so on. If you use this formula in Aug-23 and Aug-24, it will return the same value. How do I add a condition on this formula that would help it look for specific years in the same way that it looks for specific months? I know my schedule only spans (2) two years at a time so I can easily update the year range in the same way that I update the month and day specifics of the formula as I apply it to future months.

My sheet is so long it is hard to show a relevant screen shot but if this does not make sense I can make a smaller example and screen shot it.

JKL

Another way to explain the aim here:

A formula that returns the number of days in a given month/year that is included within a certain range.

If the date range was 10/05/2023 - 02/03/2024, the target return data would be per below. Each of these results below would be represented in a column named by the Month/Year and the formula can be adjusted in each month to look for that specific month in that specific year. The goal here is to ensure that months return the accurate number of days included in the range based upon the years in the range and the years in the formula.

Oct-23 = 5

Nov-23 = 30

Dec-23 = 31

Jan-24 = 31

Feb-24 = 3

Paul Newcome

Which formula are you using? The last formula I provided in the other thread does incorporate the year.

JKL

I noticed the other formula was returning an incorrect amount for the last statement when the Start and End days were within the same month. I made it work with this formula (SD = Start Date, ED = End Date)

=IF(AND(SD@row <= DATE(2023, 12, 1), MONTH(ED@row) = 12), DAY(ED@row), IF(AND(SD@row <= DATE(2023, 12, 1), ED@row >= DATE(2023, 12, 31)), 31, IF(AND(SD@row >= DATE(2023, 12, 1), ED@row <= DATE(2023, 12, 31)), (ED@row - SD@row) + 1, IF(AND([End Date]@row >= DATE(2024, 1, 1), MONTH([Start Date]@row) = 12), 31 + 1 - DAY([Start Date]@row, "0")))))

JKL

@Paul Newcome I now discovered that this formula returns the incorrect date for the following year months.....I guess the initial statement allows it to search for the months in the previous year.

JKL

My current sample schedule begins in Aug-23 and ends in Sep-24. I ran into the problem once I hit Aug-24. I was able to fix Aug-24 with this:

=IF(AND(SD@row > DATE(2023, 8, 31), MONTH(ED@row) = 8), DAY(ED@row), IF(AND(SD@row <= DATE(2024, 8, 1), ED@row >= DATE(2024, 8, 31)), 31, IF(AND(SD@row >= DATE(2024, 8, 1), ED@row <= DATE(2024, 8, 31)), (ED@row - SD@row) + 1, IF(AND([End Date]@row >= DATE(2024, 9, 1), MONTH([Start Date]@row) = 8), 31 + 1 - DAY([Start Date]@row, "0")))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.