Formula to return date of 3rd wednesday of current month?
- Thread starter jblack
- Start date
NARAYANK991
Excel Ninja
Hi ,
Assuming that A1 contains the name of the current month ( January / Jan , February / Feb ,... ) , the following array formula , entered using CTRL SHFT ENTER , will do the job :
=SMALL(IF(WEEKDAY(ROW(INDEX(A:A,0+TEXT(1&A1,"mm/dd/yy")):INDEX(A:A,30+TEXT(1&A1,"mm/dd/yyyy"))))=4,1,999)*(ROW(INDEX(A:A,0+TEXT(1&A1,"mm/dd/yy")):INDEX(A:A,30+TEXT(1&A1,"mm/dd/yyyy")))),3)
Narayan
Assuming that A1 contains the name of the current month ( January / Jan , February / Feb ,... ) , the following array formula , entered using CTRL SHFT ENTER , will do the job :
=SMALL(IF(WEEKDAY(ROW(INDEX(A:A,0+TEXT(1&A1,"mm/dd/yy")):INDEX(A:A,30+TEXT(1&A1,"mm/dd/yyyy"))))=4,1,999)*(ROW(INDEX(A:A,0+TEXT(1&A1,"mm/dd/yy")):INDEX(A:A,30+TEXT(1&A1,"mm/dd/yyyy")))),3)
Narayan
Thanks Narayan and Luke for helping with this.
Luke, could you tell me in your formula what determines the day of the week (Wednesday in this case) and the week of the month (3rd week in this case) so that if I want to change the day to Tuesday and the week to the 4th week for instance?
Luke, could you tell me in your formula what determines the day of the week (Wednesday in this case) and the week of the month (3rd week in this case) so that if I want to change the day to Tuesday and the week to the 4th week for instance?
The CHOOSE/WEEKDAY combination at the end is what chooses the day. We find the first day of the month, and then WEEKDAY tells me what day of the week it is, using Sunday = 1, Monday = 2, etc. The CHOOSE function then takes that number, and we choose how many days we need to add. So, it the first day of month is Sunday, we need to add 3 days, if Monday, add 2, etc. That's what the "3,2,1,0,6,5,4" bit at the end is for.
The "+14" is what determines which instance of that day we want. Adding 0 gives first Wednesday, +7 is 2nd Wednesday, +14 is 3rd, etc.
The "+14" is what determines which instance of that day we want. Adding 0 gives first Wednesday, +7 is 2nd Wednesday, +14 is 3rd, etc.
SirJB7
Excel Rōnin
Hi, jblack!
Assuming A1 has the nth desired occurrence of the day of the week, and B1 the desired day of the wee, try this non-array formula:
=FECHA(AÑO(HOY());MES(HOY());1)+(B$1-(DIASEM(FECHA(AÑO(HOY());MES(HOY());1))<=C$1))*7+C$1-DIASEM(FECHA(AÑO(HOY());MES(HOY());1)) -----> in english: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)+(B$1-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))<=C$1))*7+C$1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
or this one:
=FIN.MES(HOY();-1)+1+(B$1-(DIASEM(FIN.MES(HOY();-1)+1)<=C$1))*7+C$1-DIASEM(FIN.MES(HOY();-1)+1) -----> in english: =EOMONTH(TODAY(),-1)+1+(B$1-(WEEKDAY(EOMONTH(TODAY(),-1)+1)<=C$1))*7+C$1-WEEKDAY(EOMONTH(TODAY(),-1)+1)
General formula structure:
=<1st.day.of.month>+(B$1-(WEEKDAY(<1st.day.of.month>)<=C$1))*7+C$1-WEEKDAY(<1st.day.of.month>)
Regards!
Assuming A1 has the nth desired occurrence of the day of the week, and B1 the desired day of the wee, try this non-array formula:
=FECHA(AÑO(HOY());MES(HOY());1)+(B$1-(DIASEM(FECHA(AÑO(HOY());MES(HOY());1))<=C$1))*7+C$1-DIASEM(FECHA(AÑO(HOY());MES(HOY());1)) -----> in english: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)+(B$1-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))<=C$1))*7+C$1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
or this one:
=FIN.MES(HOY();-1)+1+(B$1-(DIASEM(FIN.MES(HOY();-1)+1)<=C$1))*7+C$1-DIASEM(FIN.MES(HOY();-1)+1) -----> in english: =EOMONTH(TODAY(),-1)+1+(B$1-(WEEKDAY(EOMONTH(TODAY(),-1)+1)<=C$1))*7+C$1-WEEKDAY(EOMONTH(TODAY(),-1)+1)
General formula structure:
=<1st.day.of.month>+(B$1-(WEEKDAY(<1st.day.of.month>)<=C$1))*7+C$1-WEEKDAY(<1st.day.of.month>)
Regards!