Output with excel formula (days elapsed)

[lvoh]

Hi,

I need to have the output (Excel spreadsheet) contain an Excel formula to calculate the days elapsed based on today's date. 

Column A: ID

Column B: Occurrence date

Column C: Days since occurrence date

In Excel I would put the following formula into C1 and apply to the remaining rows: =DAYS(TODAY(),B$1)

That way, each time the spreadsheet is opened, Column C reflects the number of days elapsed since the occurrence date. I know this calculation can be done in Alteryx but the result is static. 

How do I do this in Alteryx?

Thanks!

	A	B	C
1	123	20/09/21
2	124	18/09/21
3	125	15/09/21

[cmcclellan]

You can't create Excel formulas in Alteryx.  I know what you want to do, but Alteryx doesn't work like that.

Check the solution below by @pdave87 

[pdave87]

@lvoh Does below address your use case in Alteryx? Only caveat is - we have to 'Run' the workflow to create latest output in results window. Then we should see latest number of days elapsed since the date of occurrence. Let me know your thoughts 🙂 Happy solving! 

Best from,

Pratik

[pdave87]

More advanced methods would be deploying Iterative Macros. That will 100% met your expectations.

[cmcclellan]

Can you explain how an iterative macro solves this problem ?

[pdave87]

@cmcclellan sure. By setting up iterative macro for same workflow, user will be able to generate the output for specific number of days elapsed inline with occurrence. However, the workflow needs to be executed (that's a manually intervention). The iterative loop will exit based on the condition specified e.g. after 5 days have elapsed from the date of occurrence in the below screen-print. Refer both Macro and Original workflow screen-prints.

Macro:

Original 

[pdave87]

@cmcclellan I believe the steps in Excel vs. Alteryx are exactly same with or without Macro i.e. we open the days elapsed file in excel (with formula) likewise we execute the workflow (After importing the updated data). We can bring in new/updated data as a text input and set the exit condition via iterative macro (if required).

[cmcclellan]

But the question is ..... how to add formula into an Excel spreadsheet, The OP wants =DAYS(TODAY(),B$1) in Col C.  A macro won't solve that.

[pdave87]

@cmcclellan Here we go...

Save Output as .xlsx (Every time user opens this output, xlsx will execute the referenced column)

[cmcclellan]

That's great, I didn't know that 🙂