Simple Business Day Diff Macro [HELP]

[dtmedico]

Hi all -

I have spend the past hour or so trying to get this macro to work in one of my transforms (.yxmd) which contains multiple beginning and closing date options. I am at a loss right now because I cannot understand why my input questions do not work to dynamically change the fields in the formulas. I get an errors saying it doesn't recognize the other field in my transform (which has nothing to do with this macro). The logic seems sound enough for my purposes.

Any ideas? Are my actions incorrect? Am i using the wrong question tool?

I can't post my transform as the data is confidential - but if needed I can dummy it up. Let me know!

Any help would be greatly appreciated!

Thanks!!!

[MichalM]

@dtmedico 

I'm afraid the drop downs will not work within your macro this way. How do you want to select the dates used in the formula? Is it the min and max from the data stream or would you like to select these in the tool configuration?

[dtmedico]

Essentially the macro is suppose to add a column to the output computing the # of business days between two columns of dates (of the users choosing). Does that make sense?

[MichalM]

Ok, makes sense. I assume the macro should be universal and work with any data file (column names)?

[dtmedico]

That's what I was going for so anyone on my team could use it for any data set they are using in their transform. 

[MichalM]

Here's an example with a simple formula. Hopefully this will help. 

[dtmedico]

This doesn't remove weekends though, does it? 

[MichalM]

This one doesn't. To do that you'd need to do something like the below

• Generate a row per day between Date From and Date To
• Identify which day of the week they are (1 - Monday to 7 - Sunday)

ToNumber(DateTimeFormat([Date],"%u"))

• Filter out weekends
• Count them

[BenMoss · Accepted solution]

Have you seen this macro created by myself 😄

https://gallery.alteryx.com/#!app/Working-Day-Time-Difference/5a0ad875f499c708d037257c

Ben

[dtmedico]

Thank you for you help!