find number of days per month between 2 dates

Stacey Mordarski

How would I add years to this formula as well? I have a date range that includes dates from 2022 and 2024 but I only want to sum up the number of days that are within the range for Jan 2023, Feb 2023, etc.

Thanks!

Cleversheet

@Stacey Mordarski, since you only need the dates within those two months then you'd only need columns for, say, [Days in Jan 2023] and [Days in Feb 2023] as well as [Total Days], and use the applicable [Formulas] found in this sheet. Not sure what you mean by the "etc" though. In any event, you'll need a column for every month across the range you want to sum, adapting the formula in each month's column for its month number and its number of days.

Stacey Mordarski

@Cleversheet - sorry I probably didn't explain that very well! In the example below, SI Date = Start Date and RTP Date = End Date.

2023-04 SI Days should sum up the total days within that range that are in April of 2023 which should be 30 but since my start date begins in 2022 it does not account for the year. I am wondering how to add year to the formula so that give me the total days for April of 2023 in the 2023-04 SI Days column.

I also have additional columns where I need to sum up the total days in May 2023, June 2023, July 2023, Aug 2023, Sept 2023, Oct 2023, Nov 2023, and Dec 2023.

Thank you!!

Paul Newcome

@Stacey Mordarski Try this:

=IF([SI Date]@row< DATE(2023, 04, 01), IF([RTP Date]@row>= DATE(2023, 05, 01), DAY(DATE(2023, 05, 01) - 1), IF([RTP Date]@row>= DATE(2023, 04, 01), DAY([RTP Date]@row))), IF([SI Date]@row< DATE(2023, 05, 01), DAY(DATE(2023, 05, 01) - 1) - DAY([SI Date]@row)))

To adjust it for other months, in the example above each 4 is the month you want to output for and each 5 is the following month. For December, you would use 12 and 1 but add 1 to the year for the DATE functions containing the 1.

Stacey Mordarski

@Paul Newcome That worked great, thank you so much!!

Paul Newcome

@Stacey Mordarski Happy to help. 👍️

JKL

Can this formula be adjusted to account for ranges that extend beyond (2) two months? I have a very similar situation with date ranges spanning many and more than one year. I need the formula tailored to suit looking for a month in a certain year so that it does not pick up the same month in the next year.

Paul Newcome

@JKL Are you able to provide some screenshots for context?

JKL

@Paul Newcome I have noticed that this formula was producing the wrong result if the start date and end date fell within the same month.

JKL

@Paul Newcome I was able to make this work with the following formula. "SD" = Start Date and "ED" = End Date - these are "helper" columns for Start Date and End Date to remove the 8 hour work day settings due to project settings, I added a "+ 1" in the final statement to return the starting day of the task. I am replicating this across all months now to check for any errors.

=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")))))

Stacey Mordarski

@Paul Newcome, you helped me with this formula a while back but I just noticed that when the SI Date and the RTP Date are in the same month year it does not calculate correctly. I have been working at this for a while and just can't quite figure it out so I was hoping you could help me out again? Thank you!!

Paul Newcome

@Stacey Mordarski In the second to last DAY function... Try changing

DAY(DATE(2023, 8, 1) - 1)

to

DAY([RTP Date (Estimated)]@row)

NeelInNihon

Great help from this. even chat gpt was not able to provide me the soluation.

=SUMPRODUCT(--(MONTH(ROW(INDIRECT($A4 & ":" & IF($B4="",TODAY(),$B4))))=MONTH(E$1)))

whoever found this. lots of thanks to him/her.

NeelInNihon

but this is noyt working inif the year is changes to 2024