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!

Sample_29APR2025.xlsx

Datasets

Transformation

Accepted answers

GrowthNatives

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:

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
Make sure only weekdays are counted:
- Add a Formula to extract the weekday:

DateTimeFormat([Dates], "%A")
&#8203;

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! 🚀

GrowthNatives

Hi @ivoiculescu2020,

Thanks for sharing the screenshot — your Generate Rows tool is set up correctly, and it's great that weekend filtering is working!

If you're still getting incorrect day counts, especially values like 15 days between 14-Mar and 18-Mar, the issue is most likely caused by duplicate rows entering the Summarize step.

Here’s what to check:

After filtering out weekends, add a Unique Tool or Summarize using Group By: Job, Country Approver, Dates to ensure there are no duplicate daily records before counting.
In the Summarize Tool:
- Group by Job, Country Approver, or whatever your unique identifiers are.
- Count the number of distinct Dates only (not rows!).
If you joined anything back to the main data before counting days, make sure that the Join didn’t cause duplicates.

You can also use a Browse Tool after filtering weekends to visually confirm that each date between internal and country approval is only listed once per job approver.

Let me know if you'd like help reviewing the full logic or want to share a snapshot of your Summarize configuration! Happy to help you get it 100% accurate.

">⭐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! 🚀

All comments

GrowthNatives

Hi @ivoiculescu2020 ,

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

Use a Join tool to join the internal and country approval data using Job ID.
Use a Formula tool with the DateTimeDiff() function to get the total days between the two dates.
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.

Screenshot 2025-04-29 095705.png

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.

Screenshot 2025-04-29 095937.png

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

Dates.yxmd

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!

Kuha05

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

Working days calc1.yxmd

Sample_29APR2025.xlsx

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:

Make sure the data is sorted by Job before this step (use a Sort tool).
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

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:

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
Make sure only weekdays are counted:
- Add a Formula to extract the weekday:

DateTimeFormat([Dates], "%A")
&#8203;

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! 🚀

ivoiculescu2020

@GrowthNatives

Hello again,

I configured the Generate rows exactly as you indicated, I excluded Saturdays and Sundays (and this works!), however it still does not work.

I attached a screenshot of how the generate rows tool is configured in my workflow.

thanks,

Screenshot 2025-04-30 192101.png

GrowthNatives

Hi @ivoiculescu2020,

Thanks for sharing the screenshot — your Generate Rows tool is set up correctly, and it's great that weekend filtering is working!

If you're still getting incorrect day counts, especially values like 15 days between 14-Mar and 18-Mar, the issue is most likely caused by duplicate rows entering the Summarize step.

Here’s what to check:

After filtering out weekends, add a Unique Tool or Summarize using Group By: Job, Country Approver, Dates to ensure there are no duplicate daily records before counting.
In the Summarize Tool:
- Group by Job, Country Approver, or whatever your unique identifiers are.
- Count the number of distinct Dates only (not rows!).
If you joined anything back to the main data before counting days, make sure that the Join didn’t cause duplicates.

You can also use a Browse Tool after filtering weekends to visually confirm that each date between internal and country approval is only listed once per job approver.

Let me know if you'd like help reviewing the full logic or want to share a snapshot of your Summarize configuration! Happy to help you get it 100% accurate.

">⭐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! 🚀

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703