How to Group Dynamic Columns (date based)?

[Uri_Teitler]

We run a report every week to look for missing timesheets.

We need to group them by Prior Period and Current Period.

Then determine if there is any missing in those periods.

The report creates a new col each week (week beginning).

Once all missing timesheets are submitted the oldest dates drop off.

Attached is a sample of the input file and expected output file.

For this example I have assumed the "today's date" ie current period is 13/09/21 (DD/MM/YY)

Any help would be much appreciated.

[Uri_Teitler]

hi .. thanks for the follow up .. here is a snippet of more sample data.

I should have been a bit clearer, Prior Periods = (Prior Period = Any and all weeks before this week)

As you can see Ron Ross has a missing timesheet in the prior weeks but the last two are OK.

[atcodedog05]

Hi @Uri_Teitler 

Can you share the excel file which has formulas?

[atcodedog05]

Hi @Uri_Teitler 

I got an understanding of the usecase logic. You would need transpose method for this since we need to look at the previous all values. Here is how you can do it.

Workflow:

Hope this helps : )

[Maskell_Rascal]

Hi @Uri_Teitler 

Now that we understand the logic for the desired output, I believe that the transpose method that @atcodedog05 provided would be the easiest approach. I do have a workflow that I'll share using the dynamic tool functions, but I will warn that it is a bit complicated. It also requires the use the Dynamic Formula CReW Macro. I added annotations to all the tools, so you can see what the workflow is doing. 

The workflow is as follows:

Attached is the workflow and a packed version of the macro in case you need it. 

Cheers!

Phil