How to convert standard AM PM time field to Military time field for calculations

[patbuchanan]

I'm pulling in an excel spreadsheet that contain 2 columns that show 'Setup Begin Time' and 'Breakdown End Time' and I'm looking to format these 2 fields to Military time, so I can subtract 'Breakdown End Time' - 'Setup Begin Time' to track a field called 'Event Hours' to know how long event took.

I attached a screen shot of how the 2 fields look and they don't have any formatting on them and just show the time all bunched together. ex: 1:00PM

[hic]

I am not familiar with the details of "Military time", but I assume that it is standard 24-hour notation. If so, you can use

     Time#(Time, 'hh:mmTT)

to interpret the time, and

     Time(Time#(Time, 'hh:mmTT),'hh:mm')

to format it.

But then you get the numeric value of the time as dates, i.e. one day = 1 and one hour equals 1/24.

HIC

[patbuchanan]

Thank you!  Yes, sorry I was meaning 24-hour.  That worked great for providing the Breakdown and Setup time, but during the load I'm trying to subtract breakdown and setup to calculate event hours.

I searched and saw some examples with interval and tried that and have tried to add Time formats to it but have been unsuccessful.

interval((Time(Time#([Breakdown End Time],'hh:mmTT'),'hh:mm')) - (Time(Time#([Setup Begin Time],'hh:mmTT'),'hh:mm'))) as EventHours

[hic · Accepted solution]

You can use a format code with Interval() also, e.g.

Interval(Time#([Breakdown End Time],'hh:mmTT') - Time#([Setup Begin Time],'hh:mmTT'),'hh:mm') as EventHours

If you omit the format code from the interval(), QlikView will take the format code from the environment variables in the beginning of the script.

An alternative is to convert it to decimal hours:

24*(Time#([Breakdown End Time],'hh:mmTT') - Time#([Setup Begin Time],'hh:mmTT')) as EventHours

HIC

PS Read more about date formats, interval and dual values here:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual