Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
skzwane01
Contributor III
Contributor III

Convert hours to date

Hi,

I have an excel data file which has the below data and I would like to convert the data into date format.

TriggerDate
07:06.9
18:02.4
23:11.4
21:34.0
52:13.9
29:33.5

 

Please kindly assist.

 

Kind Regards,

Sanele Zwane

Labels (3)
5 Replies
sunny_talwar

Based on the input file provided, what is the number you are hoping to see? What Date should 30:03.8 correspond to?

skzwane01
Contributor III
Contributor III
Author

Hi, 

Thank you for response. Please see below data. I just noticed that there is another field in the data that can assist in deriving the actual date (Date 2).  I say this because I see some the "Date 2" data is on the correct date format however I need to make the data consistent

Based on the data below - Please assist in converting the "Date 2" to the "Date 1" format and the calculation of the Date difference. 

Hope this makes sense

Date 1Date 2Date Difference
01/09/2016 10:3831/08/2016 18:42 
08/07/2016 09:0640:34.3 
16/09/2016 13:2440:34.2 
28/07/2016 11:2126/07/2016 19:06 
29/07/2016 10:1855:54.9 
29/09/2016 13:1928/09/2016 15:59 
sunny_talwar

But what date are you expecting this to be? 40:34.3

skzwane01
Contributor III
Contributor III
Author

40:32.3 is 40 hours and 32 minutes and 3 seconds before the date :'08/07/2016 09:06'  

so I need the  result of the following calculation in a date format:(08/07/2016 09:06 40 hours and 32 minutes and 3 seconds)

sunny_talwar

Try this

Table:
LOAD [Date 1],
	 TimeStamp(If(Num([Date 2]), [Date 2], [Date 1] - Interval#([Date 2], 'hh:mm.ss'))) as [Date 2],
	 Interval(If(Num([Date 2]), [Date 1] - [Date 2], Interval#([Date 2], 'hh:mm.ss')), 'hh:mm.ss')  as [Date Difference];
LOAD * INLINE [
    Date 1, Date 2
    01/09/2016 10:38, 31/08/2016 18:42,  
    08/07/2016 09:06, 40:34.03,  
    16/09/2016 13:24, 40:34.02,  
    28/07/2016 11:21, 26/07/2016 19:06,  
    29/07/2016 10:18, 55:54.09,  
    29/09/2016 13:19, 28/09/2016 15:59,  
];