dynamic formula

[Zhenie]

Hi,

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

Example :

Input Data :

A_StartDate	A_EndDate	B_StartDate	B_EndDate	C_StartDate	C_EndDate
05/01/2022	05/02/2022	05/03/2022	05/05/2022	05/04/2022	05/07/2022

Results (Date Diff between StartDate and EndDate):

A	B	C
31	61	91

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 solution]

@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 solution]

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.

[Zhenie]

Awesome ! thanks both