8 Replies Latest reply: Oct 22, 2012 12:36 PM by Rebecca Molstad

# Interval Expression Problem

Hi All,

Can anyone diagnose why this expression is not working?

The Total Time expression is:

=Interval(Timestamp(activity_totime,'M/DD/YY HH:MM:SS TT')-Timestamp(activity_fromtime,'M/DD/YY HH:MM:SS TT'),'HH:MM')

Thanks!

• ###### Re: Interval Expression Problem

HI

Try like this

=Interval(Timestamp('9/11/2012 10:30:00 AM','M/DD/YY hh:mm:ss tt')-Timestamp('9/11/2012 11:15:00 AM','M/DD/YY hh:mm:ss tt'),'hh:mm') gives -00:45

Hope it helps

• ###### Re: Interval Expression Problem

Thanks for the help, Mayil.  Using my field names, what would be different about the statements though? Am I missing something?

• ###### Re: Interval Expression Problem

HI

Use hh:mm:ss tt instead of caps..HH:MM:SS TT

So it brings. please test it in textbox bu using both format.. you can find the difference..

• ###### Re: Interval Expression Problem

That did the trick. Thanks!!

• ###### Re: Interval Expression Problem

Qlikview expression evaluator takes MM as Month becasue M(Capital) is used for month.

m is used for minutes.

## Dates

• To describe the day, use the symbol "D" for each digit.
• To describe the month number, use the symbol "M" or "MM" for one or two digits. "MMM" denotes short month name in letters as defined by the operating system or by the override system variable MonthNames in the script. "MMMM" denotes long month name in letters as defined by the operating system or by the override system variable LongMonthNames in the script.
• To describe the year, use the symbol "Y" for each digit.
• To describe the weekday, use the symbol "W". One W will return the number of the day (e.g. 0 for Monday) as a single digit. "WW" will return the number with two digits (e.g. 02 for Wednesday). "WWW" will show the short version of the weekday name (e.g. Mon) as defined by the operating system or by the override system variable DayName in the script. "WWWW" will show the long version of the weekday name (e.g. Monday) as defined by the operating system or by the override system variable LongDayName in the script

## Times

• To describe the hours, use the symbol "h" for each digit.
• To describe the minutes, use the symbol "m" for each digit.
• To describe the seconds, use the symbol "s" for each digit.
• To describe the fractions of a second, use the symbol "f" for each digit.
• To describe the time in AM/PM format, use the symbol "tt" after the time.
• ###### Re: Interval Expression Problem

One more question.  When I export it to Excel, Excel changes the format so that an elapsed time of 45:49 (45 hours and 49 minutes) shows up as 45:49:00, or 1/1/1900 9:49:00 PM.  Is there a way to adjust the format in QlikView so that it will be exported properly?

• ###### Re: Interval Expression Problem

HI

Try like this

Interval(Timestamp('9/11/2012 10:30:00 AM','M/DD/YY hh:mm:ss tt')-Timestamp('9/11/2012 11:15:00 AM','M/DD/YY hh:mm:ss tt'),'hh:mm:ss')

may be it helps

• ###### Re: Interval Expression Problem

I tried that but then it forces me to have hh:mm:ss in the QlikView table instead of hh:mm like I want. Hmm.