How do I deal with necessary gaps in Pivot Table source data?

[HappyRabbit]

Hi there

I've created a formula whereby a column will post an end of week Friday date into a cell if a threshold is met. If however the threshold is not met, the column will post a blank into that column's row. There has to be a row spacing of five working days to each Friday as there are calculations on each day which are needed for the formula to sum that Friday's threshold.

On Pivot Tables, it will list all the dates that are over the threshold perfectly in the Pivot Table Row Field without blanks. However, because the dates are not in every cell on the source data, I cannot get the data to group so I can carry out a seasonal analysis. The Group Field is greyed out.

If I copy and paste the data to a separate column removing the gaps, all is fine - the Group Field becomes active. However, this is an impractical solution as the data has to be constantly updated and I have 60 separate columns (!) which need the same treatment every Friday.

Thank you to anyone who has a solution that can save my life!