Excel Weekday function, finding day in week

[ThomasBP]

Hi

I'm a little lost here, so hope someone can guide/help me.
I having a Sheet1 where I have 7 rows Monday to Sunday and week number 31 to 52.
The week numbers i'm getting by the formula AG5 = Weeknumber(Year!B2,2)
And then AH5 = AG5 + 1 and so on, up to week number 52.

Then I have another sheet "Year", where I have some dates and type of theme.

I then try to create a conditional formatting rule for the AG6 to BB6, where I want to add a color for the cell for the week, if I got a date that's Monday, from the "Year" sheet B6 to B24 and if the type (A6 to A24) is "Træning", so i got something like this (The same I will do for Thursday, Wednesday and so on):

But I don't know how i can build up the Match() or And() formula, can someone help ?

 

[PhilS2520]

Hi ThomasBP,

I would suggest you look at the WEEKDAY() function in the Date and Time Group. You can get the weekday number 1-7 and then compare it to what you need.

 

[ThomasBP]

Yes and have something like this, but it's not working properly and don't look at the type for the date.

[COLOR=#242729][FONT=Consolas]=IFERROR(MATCH(DATE(YEAR(Year!$B$2),1,-2)-WEEKDAY(DATE(YEAR(Year!$B$2),1,3))+AG5*7,Year!$B$6:$B$24,0),"")[/FONT][/COLOR]

Can someone maybe help with the formula?

 

[ThomasBP]

Yes and have something like this, but it's not working properly and don't look at the type for the date.

[COLOR=#242729][FONT=Consolas]=IFERROR(MATCH(DATE(YEAR(Year!$B$2),1,-2)-WEEKDAY(DATE(YEAR(Year!$B$2),1,3))+AG5*7,Year!$B$6:$B$24,0),"")[/FONT][/COLOR]

Can someone maybe help with the formula?

Okay, I'm getting closer a solution, to color the right cells - now I just need it to look at the type next to the date.
But i'm using this code atm.

=HVIS.FEJL(SAMMENLIGN(HELTAL(DATO(ÅR(Året!$B$2);1;-2)/7)*7+2+AG5*7;Året!$B$6:$B$40;0);"")

Where I change +2 to +3 and +5 to look if the day is Monday, Tuesday or Thursday.