Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

subtract times with AM and PM values

Hi all.

I have a csv file with two different columns:

START                         END

8:00 AM                       9:00 AM

10:15 AM                      12:00 M

I need to calculate the time difference in hours and minutes.

I tried:

interval(END]-[START], 'hh:mm')

And nothing happens. I think that the time fields are not well format, so I tried again:

Interval(Time#([END],'hh:mm') - Time#([START],'hh:mm'),'hh:mm')

And again, doesn't work.

Suggestions?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: subtract times with AM and PM values

you just miss the tt

have a look at format code in QlikView help

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. 
  • Arbitrary separators can be used.

Examples (Time):

hh:mm describes the time as 18:30

hh.mm.ss.ff describes the time as 18.30.00.00

hh:mm:tt describes the time as 06:30:pm

7 Replies

Re: subtract times with AM and PM values

Try this:

Interval(Time#([END],'hh:mm TT') - Time#([START],'hh:mm TT'),'hh:mm')

Highlighted
javier_florian
Contributor III

Re: subtract times with AM and PM values

Hi Chang,

You would use the follow formula:

=Time(Your_Field,'TT')

MVP
MVP

Re: subtract times with AM and PM values

you just miss the tt

have a look at format code in QlikView help

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. 
  • Arbitrary separators can be used.

Examples (Time):

hh:mm describes the time as 18:30

hh.mm.ss.ff describes the time as 18.30.00.00

hh:mm:tt describes the time as 06:30:pm

Not applicable

Re: subtract times with AM and PM values

Thanks it worked!

Re: subtract times with AM and PM values

Hahahaha mine worked but the correct answer is going to maxgro .

JK. I am glad we were able to help.

Best,

Sunny

Not applicable

Re: subtract times with AM and PM values

Sorry with the documentation reference of maxgro i also solved other questions.

You rocks Sunny!

Re: subtract times with AM and PM values