Learn more about the Alteryx Maveryx Universe recently announced at Inspire 2023!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to find the difference between two time which is in datetime format?

8 - Asteroid



I've a dataset where I've to find the difference between time (Date2 - Date1)? Below is dataset and expected output



date1                               date2

05/02/16 06:16:03 AM05/02/16 06:16:13 AM
05/02/16 06:16:12 AM05/02/16 06:17:32 AM
05/02/16 06:16:13 AM05/02/16 06:17:33 AM


Expected Output:

date1                               date2                               diff

05/02/16 06:16:03 AM05/02/16 06:16:13 AM0:00:10
05/02/16 06:16:12 AM05/02/16 06:17:32 AM0:01:20
05/02/16 06:16:13 AM05/02/16 06:17:33 AM0:01:20
11 - Bolide

I have used a formula tool to transform the time excluding PM or AM, then calculated the difference.


It does not calculate days, but it is easy to implement.


Hope it helps.

Inactive User
Not applicable

Hi Shaaz


You need to first convert those date and times to DB compatible date and times. You can use the datetime tool, but first you need to transform the AM/PM to 24 hour time. You can do that by a formula by adding 12 hours to the time value if PM. Then, use the formula DateTimeDiff([Date1],[Date2],'seconds')

5 - Atom

Could you please tell how to achieve the same but for business days? If I use my everyday formula, I get 0 values as my formula calculates business days but I need "business seconds". Since my base is business days, there are calculations that are less than a full business days and the resulto in tranforming that to seconds is 0.

This is my formula for business days:


1 + ((DateTimeDiff([Resolved],[Created],"days")*5 - (ToNumber(DateTimeFormat([Created], "%w"))-ToNumber(DateTimeFormat([Resolved], "%w")))*2) / 7) + IIF(DateTimeFormat([Resolved], "%w")=="6",-1,0) +
IIF(DateTimeFormat([Created], "%w")=="0",-1,0))


[Resolved] = Transaction completed

[Created] = Transaction created


Thanks in advance.