After a CSV export of my board, the dates are in a strange format and I cannot use them.

The date is exported with a timestamp format. To manipulate certain data like CSV export of a board, it may be useful to move from one format to another. Feel free to change the date format in the formula accordingly to your needs.

  • Formula to change the format from timestamp to AAAA/MM/JJ where A1 is the cell you want to convert: =TEXT((A1/ 86400000)+25569;"AAAA/MM/JJ HH:mm")

 

  • Formula to change from the date format AAAA/MM/JJ to timestamp, where A1 is the cell you want to convert. =(DATEVALUE(TEXT(A1;"AAAA/MM/JJ"))-25569)*86400000

 


 

For the amateurs of VBA you will find below two functions allowing to convert timestamps in date and reversely:

 

Function Date2TimestampIobeya(TimestampIobeya)

    Date2TimestampIobeya = (TimestampIobeya - 25569) * 86400000

End Function

 

Function TimestampIobeya2Date(Date)

    TimestampIobeya2Date = ((Date / 86400000) + 25569)

End Function

Application
Views: 61