# Count specific number of days across months and years

#### spacebassett

##### New Member
Hi there,

I can see something similar has been answered before here - How to count unique days in 2 different columns that are within an specific month?.

I've already built multiple spreadsheets however with the following formula though, so hesitant to change it completely unless there's no other option.

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(\$F15 & ":" & IF(\$G15="",TODAY(),\$G15))))=MONTH(Q\$1))

My query is that this has been working fine, except for when it goes over multiple years - is there an easy way of including the year in this calc? For example, this formula will give me the same reponse for days in Jun-20 as it does for Jun-21, because it doesn't seem to be taking the year into account when looking at the two dates.

Hope I'm making sense! Appreciate I may need to ditch this entirely and get my head around the above thread, but thought it was worth asking, in case I could just change the "MONTH" reference to "MONTH-YEAR" or something easy like that

Christine

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Maybe use the DateDif function

If I understand your question correctly, you can count specific days across years, as in example here. Day number to count, in C3.

Mappe1
ABCD
1
2Weekday to count:3
3
4Dates:15-12-201929-12-2021
5
6Weekdays:107
Ark1
Cell Formulas
RangeFormula
C6C6=SUMPRODUCT((WEEKDAY(C4+ROW(A1:INDEX(A1:A10000,D4-C4+1))-1)=C2)*1)

Hi there, sorry, I've not explained myself well.

I have a list of from and two dates in my rows, and months across the columns

eg.
15/7/2020

Apologies, let's try again!

I have dates from multiple years, and the formulae I have found so far that says how many days in that month fall between the from & to dates, only seem "month" related. So if the data (like below) has different years, the formula is only looking up how many days in a month and pulling through the incorrect data.

Per below, this is what I would expect it to return, but my current formula would show 2 days for both rows, as it's only looking up number of days in July, regardless of the year.

Thanks so much, I can't seem to find anything that is suitable yet. I just re-read the link I posted, but it was for something a little different, so won't work either.

Have a great day
Christine

 From To 1/07/2021​ 1/08/2021​ 15/07/2020​ 16/07/2020​ 0​ 0​ 15/07/2021​ 16/07/2021​ 2​ 0​

Hooray, I just found a solution to this. I thought I had searched all of them but it's all in the wording, isn't it.

Yay thanks brains trust!

Replies
3
Views
305
Replies
0
Views
247
Replies
4
Views
459
Replies
5
Views
392
Replies
14
Views
2K

1,206,831
Messages
6,075,118
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back