Convert hours to date

[skzwane01]

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

[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]

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 1	Date 2	Date Difference
01/09/2016 10:38	31/08/2016 18:42
08/07/2016 09:06	40:34.3
16/09/2016 13:24	40:34.2
28/07/2016 11:21	26/07/2016 19:06
29/07/2016 10:18	55:54.9
29/09/2016 13:19	28/09/2016 15:59

[sunny_talwar]

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

[skzwane01]

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,  
];