Hello. I need a formula that will give me the number of times a specific date within a date range falls on a specific day of the week. In my attached example, it's how many times does June 5 occur on a Friday between the two dates. Also as an aside, how do you input a month and day without a year? Excel automatically adds the year 2020.
Please try
=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,A2):INDEX(A:A,C2)),"dddd d mmm")=H2&TEXT(E2," d mmm")))
'05-Jun add a single quote to force date to text
This works. Thanks! A couple of related questions.
1. Same as above but how to exclude start and end dates?
2. How can I convert a day of the week to a WORKDAY value? For example convert Monday to the number 2.
3. How can I get all the dates that the month/day/day of week combo fall on?
Last edited by bird333; 12-20-2020 at 11:47 AM.
1. J2
=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,A2+1):INDEX(A:A,C2-1)),"dddd d mmm")=H2&TEXT(E2," d mmm")))
2. H3
=MATCH(H2,INDEX(TEXT(ROW(A$1:A$7),"dddd"),),)
3. K2 Custom format to dddd d mmm yyyy
=IFERROR(AGGREGATE(15,6,ROW(INDEX(A:A,A$2+1):INDEX(A:A,C$2-1))/(TEXT(ROW(INDEX(A:A,A$2+1):INDEX(A:A,C$2-1)),"dddd d mmm")=H$2&TEXT(E$2," d mmm")),ROWS(K$2:K2)),"")
Thanks again. Can you explain how the formula in 3 works?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks