Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Based on the input file provided, what is the number you are hoping to see? What Date should 30:03.8 correspond to?
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 |
But what date are you expecting this to be? 40:34.3
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)
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,
];