Hi. My data is about a group of workshop programs to be delivered during 2024. Data has 3 regions: America, Asia and Europe. There are 4 different workshop programs: A, B, C and D. Each program is delivered over 3, 4 or 5 days, and is repeated multiple times during the year for convenience. For Example: RegionProgramSession Date Program Start DateProgram End DateAMERICAB05/08/202405/08/202415/08/2024AMERICAB08/08/202405/08/202415/08/2024AMERICAB12/08/202405/08/202415/08/2024 In the table above, program B has 3 sessions delivered in 3 dates which are close to each other. I would like to create a table like below: ABCDAMERICA ASIA EUROPE In empty cells, I would like to put dates from Program Start Date field and it should be the start date of the closest program from today. I attach sample data with an additional field Program Start Date From Today. I will be thankful if anyone could help me with the workflow. TIA! ProgramData_Dummy.xlsx

How to choose the program(s) with closest date from today?

Hi. My data is about a group of workshop programs to be delivered during 2024.

Data has 3 regions: America, Asia and Europe.

There are 4 different workshop programs: A, B, C and D.

Each program is delivered over 3, 4 or 5 days, and is repeated multiple times during the year for convenience.

For Example:

Region	Program	Session Date	Program Start Date	Program End Date
AMERICA	B	05/08/2024	05/08/2024	15/08/2024
AMERICA	B	08/08/2024	05/08/2024	15/08/2024
AMERICA	B	12/08/2024	05/08/2024	15/08/2024

In the table above, program B has 3 sessions delivered in 3 dates which are close to each other.

I would like to create a table like below:

	A	B	C	D
AMERICA
ASIA
EUROPE

In empty cells, I would like to put dates from Program Start Date field and it should be the start date of the closest program from today.

I attach sample data with an additional field Program Start Date From Today. I will be thankful if anyone could help me with the workflow. TIA!

ProgramData_Dummy.xlsx

Reporting

Date Time

Preparation

Accepted answers

DataNath

Hey @Mert, how does this look? After an ascending sort on the time differential from today, we can Cross-Tab the results. We group by [Region], using the [Program] as the new field headers and then for the aggregation just take the first value i.e. the lowest in the sort:

Show soonest program start date.yxmd

All comments

DataNath

Show soonest program start date.yxmd

CoG

Will your data always only have upcoming events? If so, I agree with @DataNath 's simple and elegant solution. Otherwise, if historical events are also present, you will want to filter them out before the Sort and Cross Tab Tools.

DataNath

Good catch @CoG - hadn't considered negative time differentials!

Mert

@DataNath - Thank you very much for your quick help with a spot on solution 😊

@CoG - Thank you for the heads-up for the negative dates. I had shared the part of the workflow where I got stuck, only. In the wider workflow, exactly like your warning, I first filter out the sessions which were already delivered.

Have a nice Sunday both 🙌

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703