Output with excel formula (days elapsed)

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

Output

Date Time

Accepted answers

All comments

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 🙂

pdave87

@cmcclellan I tested the above output in xlsx. Incase, excel generates extra character "@" in the output and errors out column C then follow below steps;

1. Execute the Alteryx workflow

2. Open Excel file and perform - find & replace - @ with "" (empty space)

3. Excel provides the output in column C as expected.

Hope this helps.

My best,

Pratik

ajankowski

Is there a way to have the formula already executed when opening the Excel?

I want the result of the formula to be shown in the cell, not the formula. But when I click into the cell I want to be able to see the formula.

Right now, my formula is showing when I open the Excel output.

formula shown.png

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335