How to count the number of times a date containing a number occurs in a month, without using a range

LinuxMint · December 27, 2023, 1:14pm

I am trying to count the number of times the number 7 appears in the date for any given month, and add them so I can count down to the next occurrence - like the 7, 17, and 27 of each month. So far I have a formula which will only return a positive result if the current day has a 7 in it - such as today. My formula, =COUNTIF(B1,"*7*"), where B1 is the cell with today’s date in it, returns a 1. I have tried combining this with the MONTH() function, but that results in an error.

Thank you for any assistance.

sokol92 · December 27, 2023, 1:55pm

Try
=SUMPRODUCT(MOD(DAY(A1:A100); 10)=7)

LinuxMint · December 27, 2023, 2:01pm

Thank you. Is there any way to accomplish this without listing the dates in a range?

sokol92 · December 27, 2023, 2:10pm

Do I understand correctly: you have a date interval between DateBeg and DateEnd and you want to calculate the number of dates in this interval for which the day of the month is 7 or 17 or 27?

LinuxMint · December 27, 2023, 2:16pm

I’d like to accomplish this without a date range, if possible. But yes, the rest is correct,

sokol92 · December 27, 2023, 2:38pm

Let A1=DateBeg, A2=DateEnd, A1 and A2 are dates of one month.

=INT((DAY(A2)+3)/10)-INT((DAY(A1)+2)/10)

Example.
A1 2023-12-08
A2 2023-12-27

Result: 2 (2023-12-17 and 2023-12-27).

Villeroy · December 27, 2023, 4:16pm

Date_Pivots.ods (43.8 KB)

LinuxMint · December 27, 2023, 4:00pm

@sokol92, you did it! Here is my formula.

=IF(COUNTIF($B$1,"=7"),INT((DAY(EOMONTH(D1,0))+3)/10)-INT((DAY(DATE(YEAR(D1),MONTH(D1),DAY($B$1)))+2)/10)-1,INT((DAY(EOMONTH(D1,0))+3)/10)-INT((DAY(DATE(YEAR(D1),MONTH(D1),DAY($B$1)))+2)/10))

Where ($B$1) contains the NOW() function, and D1 contains the month in question. I have it set up so every time a date with the number 7 appears, it lowers the count of total days with sevens by an increment of 1.

Thank you so much!