Check if Week commencing contains last Friday in month

[orangebloss]

Hi all

I'm trying to work out if it's possible to calculate whether or not a given week contains the last Friday and if so place some kind of marker to flag this up so that I can determine which reporting month it should be in. Any help appreciated!

[table="width: 500, class: grid"]
[tr]
[td]W/C[/td]
[td]01/01/2018[/td]
[td]08/01/2018[/td]
[td]15/01/2018[/td]
[td]22/01/2018[/td]
[td]29/01/2018[/td]

[/tr]
[tr]
[td]Week Num[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[td]5[/td]
[/tr]
[tr]
[td]Contains last Friday of month?[/td]
[td]No[/td]
[td]No[/td]
[td]No[/td]
[td]Yes[/td]
[td]No[/td]
[/tr]
[tr]
[td]Reporting Month[/td]
[td]Jan[/td]
[td]Jan[/td]
[td]Jan[/td]
[td]Jan[/td]
[td]Feb[/td]
[/tr]
[/table]

 

[steve the fish]

Heres a couple of ways. They both rely on your date provided being a monday!

=IF(EOMONTH(B1,0)-WEEKDAY(EOMONTH(B1,0)-6)=B1+4,"Yes","No")
=IF(AND(MONTH(B1+11)<>MONTH(B1),MONTH(B1+4)=MONTH(B1)),"Yes","No")

 

[orangebloss]

Thanks! I will give it a go!