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 |
Solved! Go to Solution.
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')
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.