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

[Shaaz]

Hi,

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

Dataset:

date1                               date2

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

Expected Output:

date1                               date2                               diff

05/02/16 06:16:03 AM	05/02/16 06:16:13 AM	0:00:10
05/02/16 06:16:12 AM	05/02/16 06:17:32 AM	0:01:20
05/02/16 06:16:13 AM	05/02/16 06:17:33 AM	0:01:20

[mborriero · Accepted solution]

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]

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')

[jorgemezamorales]

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.