Count the number patterns that occur within a rolling 12 month period

Hi All

I have attached an example workbook which I need help on. The workbook has a userform which will allow the user to select a named person and enter the start date of when a pattern occurred and the number of days that it occurred for. This will then send the data onto two sheets one for availability reasons and the other to display how many times these patterns occur over.
I’m after help with the following:
If the pattern falls more than 12 months from today’s date it is highlighted in grey as it is considered as historic
If the pattern falls within the last 12 months from today’s date is considered current so highlighted in Yellow
I have tried the following formula for conditional formatting but I can’t get it to work: (unable to post code at the moment)

I’m not sure if my second ask is even possible! Is it possible to count the number of periods "P" has been entered where they fall within the last 12 months, A period would be classed as consecutive days as highlight in yellow where by weekends and cells contain "U" are ignored. The number of days in the patterns is not important, it’s the number of patterns that matter. I'm assuming that a macro would be the best option because of the potential size of the ask.

Thanks

You could put the green weekend in one condition
=OR(WEEKDAY(B$1)=7,WEEKDAY(B$1)=1)

Grey cells with "P" and older than one year
=AND(B5="P",B$1 < DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))

Yellow cells with "P" and within one year
=AND(B5="P",B$1 < TODAY())

You wouldn't have to set a one year limit for the yellow cells as the Grey condition has priority if it's before the yellow condition.

I'll have to think about your second ask.

FYI: the forum's security doesn't seem to allow posting HTML code and it thinks a < or > character is HTML code. Just put a space on either side of those characters and it should allow it.

As to counting the number of periods, see if the following solution will work. There are helper rows at the bottom of the worksheet, row 1048563 and down, populated by the formula:

Formula:

Please Login or Register  to view this content.

The number of periods is then displayed in cells QB5:QB18.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.