dynamic formula

Question

Hi,

I have a question about a dynamic solution for a formula.

Example :

Input Data :

A_StartDateA_EndDateB_StartDateB_EndDateC_StartDateC_EndDate05/01/202205/02/202205/03/202205/05/202205/04/202205/07/2022

Results (Date Diff between StartDate and EndDate):

A BC316191

This could be done using Formula tools to create column A, B, C. However, when column A, B, C is a variable (i.e. the other file has column "C", "D", "F"), is there a dynamic way to do this calculation ?

Thanks

Qiu · Accepted Answer

@Zhenie 
We can do a transpose first to bring the all the data in one column then seperate the prefix with Text to Column tool.

Then use a Multi-row formula tool to calculate the diffierence.
As long as your hearder is follwoing some naming rule, this should work.

fsalmon · Accepted Answer

Hi Zhenie,

I'm not 100% sure I understand your question. However I think that the structure of your data is a problem.

Its much easier to perform this calculation if you have all the start dates in one column and the end dates in another column.

EG:

From there you can perform a single formula in a formula tool to calculate all of the date differences.

To get the data in that format you'll need to use the cross tab and transpose tools

See the attached workflow. This will allow you to calculate for any number of variables.