LinkBack URL
About LinkBacks
Hi I need help to calculate the total number of months in the spreadsheet, not counting the overlapping months. I have attached the spreadsheet for easy reference.
Hi I need help to calculate the total number of months in the spreadsheet, not counting the overlapping months. I have attached the spreadsheet for easy reference.
Hope this help.
Identify days overlapping:
Formula:
Please Login or Register to view this content.
Count days overlapping:
Formula:Please Login or Register to view this content.
If you update to Excel 365 this formula is possible
Please try for the different days:Formula:Please Login or Register to view this content.
Great formula Hans, but your result diverge from mine 1029 <> 1033, if taking out the +1 of your formula the result gets close to mine.
Formula:Please Login or Register to view this content.
Do you have any consideration for this divergent results?
1033 is correct.
In 2022: From 1 Jan to 31 Dec = 365 days
In 2023: From 1 Jan to 31 Dec = 365 days
In 2024: From 2 Sep to 31 Dec = 121 days (29 in Sept, 31 in Okt, 30 in Nov and 31 in Dec)
In 2000: From 1 Jan to 30 Jun = 182 days (31 in Jan, 29 in Feb, 31 in Mar, 30 in Apr, 31 in May and 30 in Jun)
365+365+121+182 = 1033.
@DJunqueira, in your formula you don't include the last day of each period.
Last edited by HansDouwe; 01-18-2024 at 02:09 AM.
Array formulas
daysFormula:Please Login or Register to view this content.
monthsFormula:Please Login or Register to view this content.
No array formulas
daysFormula:Please Login or Register to view this content.
monthsFormula:Please Login or Register to view this content.
Last edited by Czeslaw; 01-18-2024 at 06:01 AM.
Originally Posted by Czeslaw
Array formulas
daysFormula:Please Login or Register to view this content.
monthsFormula:Please Login or Register to view this content.
No array formulas
daysFormula:Please Login or Register to view this content.
monthsFormula:Please Login or Register to view this content.
This cannot work as it counted the overlapping months.
And what about the other 2 solutions?
your solution works fine. I will do more sample checks on other years as well. Thanks so much!
You are Welcome!
Thanks for the feedback. Glad to have helped..
If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Right, right, some how it isn't 'my' formula, I just blindly credit Excel to count. hummm.
Tks for wake me up for this obvious matter.
hi everyone, I allow me to propose another possible solution (assuming periods less than 365 days):
In case you need considering more large periods, only need to edit the ROW expression.
Please Login or Register to view this content.
I hope it result useful.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks