String fields: days difference

[ivoiculescu2020]

Hello team,

Can you please help me with the following scenario? I have these 2 fields which are configured as DateTime and when I try to apply the Daysdifference formula it doesn't calculate correctly.

My report shows the date when shipments were generated and the dates when they were shipped. I need to know what is the difference in days between the date when the shipment was shipped and the date when it was generated. This way, I will know if there are any delays.

Example:

Shipment generated on 07-Mar-2025 at 10pm my time

Shipment shipped on 10-Mar-2025 at 06am my time

Days difference: 2 days (instead of 3)

Is it because it calculates a 24h day? How can I make this formula work to show the correct difference.

Also, I tried to change the dates to string fields and show only the date (without the time), however I am unable to calculate the difference (again)

Any insight you can provide is welcome and appreciated!

Thank you!

[GrowthNatives · Accepted solution]

Hey @ivoiculescu2020 , 
I see you're running into an issue with days difference.

What's Going Wrong?

When you use:

DateTimeDiff([Shipped], [Generated], 'days')

It calculates full 24-hour periods, not calendar days. So if the difference is 2 days and 8 hours, it still counts as 2.

What You Actually Want: Calendar Days Difference

To count the difference in dates regardless of the time (i.e., treat both timestamps as just dates), you can do:

Truncate Time Portion

Use DateTimeTrim() to drop the time:

DateTimeDiff(DateTimeTrim([Shipped], 'day'), DateTimeTrim([Generated], 'day'), 'days')

 This will give you:

• 07-Mar-2025 10:00 PM → 07-Mar-2025

• 10-Mar-2025 06:00 AM → 10-Mar-2025

• Result: 3 days
  

If you want the result to be 2 in this case, keep reading...

---

Want to Exclude the Day it Was Generated?

If your business rule is to exclude the day of generation (like a 10 PM shipment on March 7 doesn't count as a "working" day), just subtract one:

DateTimeDiff(DateTimeTrim([Shipped], 'day'), DateTimeTrim([Generated], 'day'), 'days') - 1

This way:

• March 7 (generated) → March 10 (shipped) = 3 - 1 = 2 days
  
  

❌ Why Changing to String Doesn’t Work

Once you convert dates to string, Alteryx can't do any math on them unless you convert them back to DateTime.

So it’s better to keep them as DateTime, and just use DateTimeTrim() to remove the time component.

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 

Thank you so much for your prompt help.

It worked!!!! I marked your response as a solution.

Kind regards,

Ioana