LibreOffice: Unix time to date value

I am using the great BudgetWatch app to keep track of my expenses on some projects, it works smooth and I am able to export the data into CSV, JSON or even .zip file with an enclosed CSV and pictures of the recipes.

My only problem when opening with my LibreOffice calc spread sheet is that “date” is written in Unix time stamp (epoch time or POSIX time) or  in miliseconds as noticed in the source code so in order to convert it just did the next with in LibreOffice.

=((_UNIXTIME_+(1000*60*60*_UTC_differential_))/(1000*60*60*2*24))+DATEVALUE("1/1/1970")

explanation:

  • _UNIXTIME_ : the date provided by the app in miliseconds
  • _UTC_differential_ : +2 hours in my case of UTC differential.
  • 1000*60*60*24: 1000 miliseconds per second, 60 seconds per minute, 60 minutes,  24 hours per day,  gives me the number of days in the date.
  • DATEVALUE(“_some_text_here_”) gives me the days given a LibreOffice date format provided text
  • 1/1/1970 is the date from which Unix time encoding as number starts counting as date 0