Sorry, I can’t upload my example because I need 3 points to do so. But you can copy/paste the semi-colon separated values below, save it as a .csv file then open it in LibreOffice spreadsheet and delimit on semicolons only (since there are commas in the formulas).
When I download Twitter stats, I get a .csv file that contains this date and time format (in one cell):
2014-11-02 01:58 +0000
Their date and time is provided in Greenwich Mean Time, but I want the data to display in my local date and time (GMT - 7, Arizona USA).
I concocted a time-consuming and manual workaround where I delimit/break apart the contents of the original cell so that the info is spread across 12 columns, like this:
Begin copy paste into .csv file here:
a;b;c;d orig gmt;e date only;f hour;g minute;h gmt;i gmt-7;j azHour;k azTime;l azDate
528727735825601000;https://twitter.com/radiophoenix/status/528727735825600513;Tune in now (7-9 p.m.) for @HipRawkNation http://t.co/UHeZPRk6Ls;2014-11-02 01:58 +0000;2014-11-02;01;58;+0000;=F2-7;=IF(F2<7, F2+24-7-12+12, F2-7);=TIME(J2, G2, 0);=IF(F2<7, (E2-1), E2)
528697813476340000;https://twitter.com/radiophoenix/status/528697813476339712;Now Playing The Revelations - How Could You Walk Away (Featuring Tre Williams) http://t.co/vzLPekOpsL;2014-11-01 23:59 +0000;2014-11-01;23;59;+0000;=F3-7;=IF(F3<7, F3+24-7-12+12, F3-7);=TIME(J3, G3, 0);=IF(F3<7, (E3-1), E3)
“separated by other = ;”
format columns E as date Y/M/D
The date should change in the first row of data (from Nov. 2 to Nov. 1, 2014), but not in the second (should remain Nov. 1, 2014).
End copy paste.^^
I really do not want to go through this formatting, etc. every time I download new data from Twitter. Is it possible to update both the date and the time in one or two lines? Math is NOT my strong suit so I’ve burned hours on something you can probably whip up in 5 minutes.
PS I am using LibreOffice 126.96.36.199
Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21
Mac OS 10.6.8