Days difference

[ivoiculescu2020]

Hello team,

I need your help again to figure out how to get the amount of days between 2 approval dates: a label is generated and needs to be approved in 2 parts: internal approval and country/language approval. The report I try to compile will need to show the amount of days it took the country approvers to sign off on the label since the internal approver put in their approval (excluding the weekends!)

I attached a sample of my report. The column in yellow shows how the data should look like.

I tried to group by Job, Max_internal approval date and group by country approval date but it does not show me ANY results

I tried to group by Job, Max_internal approval date and MAX_country approval date but it does not show me the right data because the country approvers don't approve the same day so this way would exclude data.

Thank you for your help!

[GrowthNatives]

Hi @ivoiculescu2020 ,

You can calculate the number of days between internal and country approvals (excluding weekends) with this approach:

1. Use a Join tool to join the internal and country approval data using Job ID.

2. Use a Formula tool with the DateTimeDiff() function to get the total days between the two dates.

3. To exclude weekends:

   • Use a Generate Rows tool to create a row for each day between the two dates.

   • Use a DateTimeFormat() + Filter tool to exclude Saturdays and Sundays.

   • Then summarize to count the number of working days.

This way, you’ll get accurate day counts even if country approvals happen on different dates.

⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀

[ivoiculescu2020]

Hello @GrowthNatives 

I tried the join tool but it doesn't help much because the step 1 approval date still shows one time next to the internal approver. It really doesn't change the data.

Maybe I misunderstand your instructions?

Also, the Generate rows tool: can you give me a few additional pointers please? I didn't use it at all until now and I would like to make sure I know how to properly use it.

Thank you!

Regards,

Ioana

[davidskaife]

Hi @ivoiculescu2020 

Here is a way of doing it. I've annotated the tools so you know whats going on.

The Generate Rows tool is setup like the below, you're creating a new field (called Dates in this instance) and it will increment through, starting from internal approval date, creating a new row until its equal to the country approval date. This can then be used to filter out weekends.

NOTE: This can generate a vast amount of rows, if the difference between dates is huge, so it can be quite a process intensive/resource consuming tool..

Finally this will calculate the days including the start and end dates, in order to match your provided data i've added a Formula tool on to decrease the result by 1

Hope this helps!

Edit: This should work for multiple jobs in one dataset as well - you may have to change the Append tool to a Join if that is the case, as you'll need to match back on more than one field

[ivoiculescu2020]

thank you so much @davidskaife for your prompt reply

Unfortunately, the multi-row formula tool does not work for me.

The Internal approval field is set as Date but it just does not fill up the rest of the column as it should. anything I should check in particular that could prevent this tool from working?

thank you again!

[davidskaife]

@ivoiculescu2020 

Can you share what you have, or screenshot of the tool config?

[Kuha05]

I found another way of doing this. Let me know if it doesn't work for you.

[GrowthNatives]

Hi @ivoiculescu2020 

Thanks for the update!

If the Multi-Row Formula Tool isn't filling the internal approval date as expected, please check the following:

1. Make sure the data is sorted by Job before this step (use a Sort tool).
2. In the Multi-Row Formula Tool:

   • Set “Update Existing Field” for the internal approval date.

   • Use Group By: Job so it resets for each job.

   • Use a formula like:

IF ISNULL([Internal Approval Date]) THEN [Row-1:Internal Approval Date] ELSE [Internal Approval Date]

• Make sure the first row of each group has the internal approval date present (otherwise, nothing to copy down).
• Confirm the field type is Date.

⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀

[ivoiculescu2020]

@GrowthNatives 

Hello,

Thank you so much for the additional instructions.

I followed them but I must be doing something wrong because the number of days to country approval are not accurate.

For example, the job had the internal approval on 14-Mar and the country approval on 18-Mar and the number of days to country approval was "calculated" as 15. Something is not right. I can't figure out what.

If you have any other tips or tricks, I would appreciate it very much.

Thank you!

[GrowthNatives · Accepted solution]

Hi @ivoiculescu2020 ,
Thanks for the follow-up!

If you're seeing a difference of 15 days between 14-Mar and 18-Mar, it likely means the workflow is not filtering weekends correctly or is double-counting rows in the Generate Rows step.

Please check the following:

1. Verify the Generate Rows setup:

   • Start at [Step 1 - internal approval date]

   • End at [Step 2 - country approval date]

   • Use DateTimeAdd([Dates], 1, "days") as the loop expression

2. Make sure only weekdays are counted:
   • Add a Formula to extract the weekday:

DateTimeFormat([Dates], "%A")
​

• Then filter out Saturday and Sunday

3. Double-check summarization:

• After filtering weekends, use Summarize to count Dates grouped by Job and Approver

• Then subtract 1 using a Formula to exclude either the start or end date (depending on how you want it calculated)

If Generate Rows is looping from 14-Mar to 18-Mar, and weekends aren't filtered, you'll get 5 rows. If it's looping incorrectly or not trimming duplicates, that could inflate the count.

⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀