in tradingview if I export data, the time is in this format on the csv. 2019-08-28T19:00:00Z and the time is off by 4 hr, what can I do to make it 8/28/19 15:00.
thanks.
in tradingview if I export data, the time is in this format on the csv. 2019-08-28T19:00:00Z and the time is off by 4 hr, what can I do to make it 8/28/19 15:00.
thanks.
I did it with the following steps:
- importing into excel
- (probably not needed in your case if the file imports in proper columns) tehxt to columns wit coma as separator (you probably have it already, but we have semicolon as default separator in Poland)
- Changing all "Z" into an empty string:
ctrl+H
Z in search field
leave empty replace field
- Changing "T" into space
ctrl+H
T in search field
(single space) in replace field
- write in some cell =-4/24
- copy this cell with Ctrl+C
- select column with dates
- paste special (open paste special dialog and select "Add" from the options at the bottom)
- if you obtained numbers not dates (like 43705.625 or similar) format the cells custom as "m/dd/yy hh:mm"
Shall be ready by now
Best Regards,
Kaper
misread question
Another way of doing it, use this Formula;
Formula:Please Login or Register to view this content.
Right click on the cell, format cells as Custom - m/dd/yy hh:mm
I made an function in VBA that converts this ISO8601 date
This function can either be used in the worksheet to give the outcome which you need to format (same like PaulSP solution)Please Login or Register to view this content.
Or you can make te function part of any existing code you have to import this type of data
I see you do not have a excel version in your profile but if you are on a more recent version (2010 or higher) you can also use powerquery to import the CSV files.
PowerQuery will automaticly recognize the ISO8601 date format and also automaticly converts the time to your local timezone (based on pc settings)
To explain the timeformat the Z at the end represents "Zulu time" which is simular to UTC or GMT (Greenwich Mean Time), This is the standard time to which all time zones are derived from.
so in your case you are -4 hours of zulu time. and powerquery will interpret that for you and change the date
see instruction on how to use powerquery (called get and transform for excel 2013 and later version)
https://www.myexcelonline.com/blog/i...get-transform/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks