excel calculate number of days per month in certain period

1
votes

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:

sheet

This is results that I am expecting:

expected results

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:

manual entry

  1. 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...

wrong manaul entry

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!

2
excel
What is your expected result? – Timothy Alexis Vass
sorry I didn't put that.. most important thing. It should be jan2020 til oct2020 0 as result, than Nov2020 - 20, Dec2020 - 31, Jan2021 - 31... and so till Nov2021 – user13401262
Update your question with all the relevant information (and preferably a markdown sample data example table instead of a screenshot). – JvdV
apologies, my bad, I just did. – user13401262
are column headers formatted as date? – AnilGoyal

2 Answers

2
votes

I am just posting another IF based variation which I had worked out.

=IF(OR(AND(C$1>$A2,$B2>C$1),AND(EOMONTH(C$1,0)>$A2,$B2>EOMONTH(C$1,0))),MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1)+1,0)

Primary condition is to check whether any date mentioned on row 1 (within the month) is intersecting the either start or end date specified in cell A2 and B2. Once this is cleared then the primary logic which you had posted works just fine.

You will have to change the argument separators as per your locale!

Edit-

@AnilGoyal spotted the formula error and I am posting the revised formula. I have put those missing AND conditions in OR. In addition to that the formula would have missed a boundary condition where date was first or last day of the month.

=IF(OR(AND(C$1>=$A2,C$1<=$B2),AND(EOMONTH(C$1,0)>=$A2,EOMONTH(C$1,0)<=$B2),AND($A2<=EOMONTH(C$1,0),$A2>=C$1),AND($B2<=EOMONTH(C$1,0),$B2>=C$1)),MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1)+1,0)

Overall, OP's original formula shall work well. As it turns out below formula seems to work just fine as well. =MAX(0,MIN($B2,EOMONTH(E$1,0))-MAX($A2,E$1)+1)

2
votes

ENTER THIS FORMULA IN C2

=IF(AND(C$1<MIN($A2,$B2),MONTH($A2)>MONTH(C$1)),0,IF(C$1>MAX($A2,$B2), 0,IF(C$1>=MIN(C$1,$A2),MIN(EOMONTH(C$1,0),$B2)-MAX($A2,C$1)+1,-MIN($B2,C$1)+$A2+1)))

enter image description here