Calculate difference between two Date and Time

[rakeshkumar1890]

Hi guys,

I am facing a challenge to calculate how many average days and time are taken by user to complete the task.

right now I am using this calculation but can't correct output on averaging this

This calculation is without averaging

(date([Closing Date])-date([Creation Date]))&':'&time([Closing Time]-[Creation Time])

Month	Ticket No	Creation Date	Closing Date	Creation Time	Closing Time	Avg Time Taken By User (Days:Hours:Minute:Second)
Feb	500	43151	43151	-53322.4896875	-53322.327672222	00:03:53:18
Feb	501	43151	43151	-53322.4224537037	-53322.265895336	00:03:45:26
Feb	502	43151	43152	-53322.342650463	-53322.455276007	01:21:17:49

if Collapse by month then what will be the Avg Time Taken by user?

after that I was used this calculation

interval(avg((date([Closing Date])-date([Creation Date]))),'DD') &': '&Interval(Avg(time([Closing Time]-[Creation Time])),'hh:mm:ss')

but get incorrect output on third case

Month	Ticket No	Creation Date	Closing Date	Creation Time	Closing Time	Avg Time Taken By User (Days:Hours:Minute:Second)
Feb	500	43151	43151	-53322.4896875	-53322.327672222	00:03:53:18
Feb	501	43151	43151	-53322.4224537037	-53322.265895336	00:03:45:26
Feb	502	43151	43152	-53322.342650463	-53322.455276007	01: -02:42:10

[rakeshkumar1890 · Accepted solution]

Now I'm going to close this thread

I have the solution which I was required

On Script level

Timestamp#(date([Creation Date])&' '&time([Creation Time]),'DD-MM-YYYY hh:mm:ss')  as [Creation TimeStamp],

Timestamp#(date([Closing Date])&' '&time([Closing Time]),'DD-MM-YYYY hh:mm:ss') as [Closing TimeStamp],

after that on Expression

Interval(avg(([Closing TimeStamp]-[Creation TimeStamp])),'DD:hh:mm:ss')

Thanks