convert historical data timestamp

[staycool3_a]

hi,

date time
1/4/17 050100
1/4/17 050200
1/4/17 050300
1/4/17 050400
1/4/17 050500
1/4/17 050600
1/4/17 050700
1/4/17 050800


above is an export from NT. I'm trying to use this in excel, however, the time values, given that they are hour minute format, how do I tell excel that its 5 am and 1 minute for the first value and so on...

I'm dividing the above time column cell by 235900 and it gives me:

date time converted
1/4/17 050100 5:05:49 AM
1/4/17 050200 5:06:26 AM
1/4/17 050300 5:07:03 AM
1/4/17 050400 5:07:39 AM
1/4/17 050500 5:08:16 AM
1/4/17 050600 5:08:53 AM


which doesnt match up

[NinjaTrader_Jason]

Hello calhawk01,

Can you please clarify how you export the data. Do you export historical data via Control Center-->Tools-->Historical Data Manager-->Export?

Unfortunately I am not sure how Excel would recognize those values as time. Perhaps other users are more familiar with Excel and know a way.

[staycool3_a]

Hello calhawk01,

Can you please clarify how you export the data. Do you export historical data via Control Center-->Tools-->Historical Data Manager-->Export?

Unfortunately I am not sure how Excel would recognize those values as time. Perhaps other users are more familiar with Excel and know a way.

hi yes, i used Control Center-->Tools-->Historical Data Manager-->Export

Anyone out there know how to convert the minutes to a regular excel format? What do i have to divide it by?

[staycool3_a]

hi yes, i used Control Center-->Tools-->Historical Data Manager-->Export

Anyone out there know how to convert the minutes to a regular excel format? What do i have to divide it by?

=TEXT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2)),"dd/mm/yyyy hh:mm:ss")

just leaving this here, in case someone else needs to use it.

in column A:

date
20170101174700

the above needs to be merged bc when you export from NT, you get: 20170101 174700 (notice the space between date and time).