Count specific number of days across months and years

[spacebassett]

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

Thanks in advance for access to your brains! I get a headache just reading the solution threads...
Christine

 

[alansidman]

Maybe use the DateDif function

MS Excel: How to use the DATEDIF Function (WS)

This Excel tutorial explains how to use the Excel DATEDIF function with syntax and examples. The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

 

[ebea]

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

Mappe1
	A	B	C	D
1
2		Weekday to count:	3
3
4		Dates:	15-12-2019	29-12-2021
5
6		Weekdays:	107
Ark1

Cell Formulas
Range	Formula
C6	C6	=SUMPRODUCT((WEEKDAY(C4+ROW(A1:INDEX(A1:A10000,D4-C4+1))-1)=C2)*1)

 

[spacebassett]

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

 

[spacebassett]

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​

 

[spacebassett]

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.

Count Days in a month in a given range

Hello, could anyone help me with a formula? I need to count the number of days in given months between a period range. For example Start Date End Date Oct 17 Nov 17 06/02/17 - 21/10/17 21 06/12/16 - 27/10/17 27 06/03/17 - 01/12/17 31 30 I have tried...

www.mrexcel.com

Yay thanks brains trust!