Put human readable dates in csv export of Time Reports
Answered
We have to post process CSV exports of Time Reports because YT outputs Unix timestamp format. We have to go to a converter and copy/paste the date in by hand. Why would you produce a report that makes us do that much hand work? Please consider outputting the date in human readable format. Thanks.
Please sign in to leave a comment.
Hi David!,
Thank you very much for your patience!
The idea was to improve import to another issue trackers. However, I beleive we'll make it possible to expore issues in human readable format. Here is the feature request https://youtrack.jetbrains.com/issue/JT-19016 .
Just in case, here is an example of converter tool http://currentmillis.com/ you can use.
By hand? Good Lord.
You can create a date-time column in Excel with this formula:
=[Timestamp cell]/86400000+DATE(1970,1,1)
Gary - your answer doesn't work for me, the dates appear in Excel in the text format "Thursday, January 8, 2015 4:00:57 PM UTC" I'm pretty good at Excel formulae, but converting that into a Datetime is beating me currently :-(
Here is an Excel formula which converts the dates output from YouTrack CSV export into an Excel Date:
=DATEVALUE(MID(C2,FIND("|",SUBSTITUTE(C2," ","|",2))+1,-1+FIND("|",SUBSTITUTE(C2,",","|",2))-FIND("|",SUBSTITUTE(C2," ","|",2))) & " " & MID(C2,FIND("|",SUBSTITUTE(C2," ","|",1))+1,-1+FIND("|",SUBSTITUTE(C2," ","|",2))-FIND("|",SUBSTITUTE(C2," ","|",1))) & " " & MID(C2,FIND("|",SUBSTITUTE(C2," ","|",3))+1,FIND("|",SUBSTITUTE(C2," ","|",4))-FIND("|",SUBSTITUTE(C2," ","|",3))))
Substitute "C2" with the cell reference containing the YouTrack Date throughout. Don't try to understand it lol, just trust me that it works :-)