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.

4 comments
Comment actions Permalink
Official 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.

Comment actions Permalink

By hand? Good Lord.
You can create a date-time column in Excel with this formula:
=[Timestamp cell]/86400000+DATE(1970,1,1)

0
Comment actions Permalink

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 :-(

0
Comment actions Permalink

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 :-)

0

Please sign in to leave a comment.