Help combing formula

[Liverlee]

Hi i have set up a calendar tracker

This formula works for recurring weekly appointments where 1 = Mon, 2= tue, wed = 3 etc etc

=IF(AB$12=$G12,1,IF(AB$12=$H12,2,IF(AB$12=$I12,3,IF(AB$12=$J12,4, IF(AB$12=$K12,5,"")))))

On row Ab1 I have week of month set up, i.e 1,2,3,4,5.

So i'd like to be able to set up the above formula where you can select week day (either 1 to 5) and then week of month ( either 1 to 5)

anyone have any ideas.

 

[Liverlee]

Hi Jason thanks for your time and patience. I'm sure there's a better way but i'm an excel novice as are my colleagues.

Anyway to answer your question.

Row 1 formula is =AB5-AB2+1 for the week of month
Row 2 formula is =WEEKNUM(AB3)
Row 3 formula is =DATE(YEAR(AB10),MONTH(AB10),1)
Row 5 formula is =WEEKNUM(AB10)

Row 10 is the calendar dates formatted to show as ddd. (mon, tue, wed)

My idea was if you clicked q13 with a 1= mon a formula would search row 10 for for all the mondays and if you click v13 with a 1 = 1st week of month then it would also search row 1 for all the week 1's and if both conditions were met you'd get a series of 'trues' or 'x' along ab13 to ih13.

I dont know.

 

[jasonb75]

Trying to simplify it, see if this gives you something to work with, in a blank sheet, enter the date 1/1/2019 in C2, then drag right for about 20 columns so that the date increases.
Enter this formula into C2, then drag right under the dates, it will show FALSE in every cell to start with.

=IF(AND(IF($B2<>"",(WEEKNUM(C$1)-WEEKNUM(EOMONTH(C$1,-1)+1)+1)=$B2,1),TEXT(C$1,"ddd")=$A2),TRUE,FALSE)

Type Mon into A2 and all of the mondays should now change to TRUE.
Type 2 into B2 and only monday of week 2 will be TRUE.

Is that anything close to what you're attempting?

 

[Liverlee]

Ahh Jason, you're a genius!! Just tried it on a blank worksheet and it works perfectly. Thank you so much. Added bonus is i'll be able to trim some of the columns out of my spreadsheet too.