Time Difference

[#1]

Dear,

I have two times values in two different columns and i want to calculate time difference between them for example :

Start_Time End_Time Total_Duration

10:30am 12:00pm 1:30

03:00pm 04:53pm 1:53

09:00am 03:00pm 6:00

Please guide me the expression for Total_Duration.

[Miguel_Angel_Baeyens]

Hello,

If your format is "10:00pm", then the following should work

=Interval(Time(Time#('12:00pm', 'hh:mmtt')) - Time(Time#('10:30am', 'hh:mmtt')), 'hh:mm')

Here is key the format the fields have.

Hope this helps.

[#3]

Its show - (Dash) only and my script is given below:

ToDo_Tab:

load

Time([Start Time]) as ToDO_Start_Time,

Time([Last Time Modified]) as ToDO_Last_Time_Modified;

SELECT [$(vTablePrefix)To-do].No_,

[$(vTablePrefix)To-do].[Start Time],

[$(vTablePrefix)To-do].[Version No_],

[$(vTablePrefix)To-do].[Opportunity Entry No_],

[$(vTablePrefix)To-do].[Last Date Modified],

[$(vTablePrefix)To-do].[Last Time Modified],

[$(vTablePrefix)To-do].Location,

[$(vTablePrefix)To-do].[Organizer To-do No_],

[$(vTablePrefix)To-do].[Interaction Template Code],

[$(vTablePrefix)To-do].Subject,

[$(vTablePrefix)To-do].Department

FROM [$(vTablePrefix)To-do] ;

Dummy_todo:

load

ToDO_Start_Time as DToDO_Start_Time,

ToDO_Last_Time_Modified AS DToDO_Last_Time_Modified,

Interval(Time(Time#(ToDO_Last_Time_Modified, 'hh:mmtt')) - Time(Time#(ToDO_Start_Time, 'hh:mmtt')), 'hh:mm')as DToDO_Mt_Time

resident ToDo_Tab

;

[#4]

Date format is

1/1/1754 2:00:00 PM 1/1/1754 11:38:27 AM

1/1/1754 2:00:00 PM 1/1/1754 11:38:27 AM

1/1/1753 12:00:00 AM 1/1/1754 10:01:15 AM

1/1/1753 12:00:00 AM 1/1/1754 10:01:15 AM

1/1/1753 12:00:00 AM 1/1/1754 11:27:28 AM

1/1/1753 12:00:00 AM 1/1/1754 11:27:28 AM

1/1/1754 10:30:00 AM 1/1/1754 8:24:31 AM

1/1/1754 10:30:00 AM 1/1/1754 8:24:31 AM

1/1/1754 10:30:00 AM 1/1/1754 4:35:31 PM

1/1/1754 10:30:00 AM 1/1/1754 4:35:31 PM

1/1/1753 12:00:00 AM 1/1/1754 2:01:07 PM

[Miguel_Angel_Baeyens]

With proper formatting, Interval() should work

=Interval(Time(TimeStamp#('1/1/1754 2:00:00 PM', 'D/M/YYYY hh:mm:ss TT')) - Time(TimeStamp#('1/1/1754 11:38:27 AM', 'D/M/YYYY hh:mm:ss TT')), 'hh:mm')

That returns "02:21".

Hope that helps

[#6]

Dear your example is working but when i used variable in place the of value its shows -(dash) only, statement is given below:

Interval(Time(TimeStamp#([Last Time Modified], 'D/M/YYYY hh:mm:ss TT')) - Time(TimeStamp#([Start Time], 'D/M/YYYY hh:mm:ss TT')), 'hh:mm') as ToDo_TimeDuration,

[Miguel_Angel_Baeyens]

A couple of things here:

You are already formatting the time fields

Time([Start Time]) as ToDO_Start_Time

By default, this will return a "hh:mm:ss" format. Is this formatting working?

My format above was used in your raw format posted by you previously ("1/1/1754 11:38:27 AM"), not the formatted one. Time() of this value, however, returns nothing.

Time('1/1/1754 11:38:27 AM') AS Field,// This will return an errorTime(Time#('1/1/1754 11:38:27 AM', 'D/M/YYYY hh:mm:ss TT')) AS Field2 // This will return "11:38:27"

Anyway, Interval() will do, so please, do check your actual time format in the tables you have already loaded. In case you need further advice, please specify the format you are using in your tables and the format you'd like to get.

ToDo_Tab:LOAD Time(TimeStamp#([Start Time], 'D/M/YYYY hh:mm:ss TT')) AS ToDO_Start_Time, Time(TimeStamp#([Last Time Modified], 'D/M/YYYY hh:mm:ss TT')) AS ToDO_Last_Time_Modified, Interval(Time(TimeStamp#([Last Time Modified], 'D/M/YYYY hh:mm:ss TT')) - Time(TimeStamp#([Start Time], 'D/M/YYYY hh:mm:ss TT')), 'hh:mm') AS DToDO_Mt_Time;SQL SELECT ... // Rest of the script here

In this case, you don't need the second RESIDENT load.