excel calculate number of days per month in certain period

I know this has been asked many times.. but I cannot make it work for my scenario. I need to calculate number of days occupancy between 2 dates, but I need to consider year as well... (IE. jan-2020 till dec 2021.

My formula for calculations is: =MAX(0; MIN(EOMONTH(C$1; 0); $B2)-MAX(C$1; $A2) +1) where C1 is month that I am looking for, B2 is end date, A2 is start date...

here is my screenshot:

This is results that I am expecting:

I think I am having issue with formatting that I don't know how to overcome... with more testing it looks like my formula was fine in the first place:

1. I create blank sheet and I manually add start dates - end dates and header column months to try formula, format it like date (with * as date - *14.3.20) and it worked fine... this is my screenshot:

2. When I copy paste start dates and end dates from different sheet (I already have thousands of start-end dates... it would be impossible to manually enter all of them). Than I paste special - just values, then in format I choose date, same format as my testing excel sheet... nothing it simply won't work... how I can format dates on pasted values? Here is screenshot that it is wrong...

I selected start date column and end date column than:

Data > Text to Columns Next, Next, then select Date 'DMY'.... did the trick.

Start and end dates are converted back to dates and everything started working for me....

Dates where imported from database and they had leading space in front of it... that is why calculations failed. Once they are back to real dates everything started working...

Thanks for help guys!

https://stackoverflow.com/questions/65899292/excel-calculate-number-of-days-per-month-in-certain-period January 26, 2021 at 06:06PM