Change GMT date and time to GMT minus 7

[pamelar987]

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)

NOTES:
“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.

Thank you!

PS I am using LibreOffice 3.5.5.3
Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21
Mac OS 10.6.8

[pamelar987]

I tried a few more ideas, but still have not found a one-cell solution. I’m still breaking the date apart into multiple columns (but am down to 4 instead of 8). Would love to know how to do this more simply so still looking for feedback/answers.

Cut and paste the following into a .csv file:

original date GMT minus 7 – date hour (format as number -123) minute concatenate hour plus minute
2014-11-01 01:58 +000 =IF(VALUE(MID($A2,12,2))<7,VALUE(MID($A2,1,10))-1,VALUE(MID($A2,1,10))) =IF(VALUE(MID($A2,12,2))<7,VALUE((MID($A2,12,2))-7+24),VALUE((MID($A2,12,2))-7)) =MID($A2, 15, 2) =TIME(C2,D2,“00”)
2014-11-01 01:58 +000 =IF(VALUE(MID($A3,12,2))<7,VALUE(MID($A3,1,10))-1,VALUE(MID($A3,1,10))) =IF(VALUE(MID($A3,12,2))<7,VALUE((MID($A3,12,2))-7+24),VALUE((MID($A3,12,2))-7)) =MID($A3, 15, 2) =TIME(C3,D3,“00”)
2015-01-01 00:00 +000 =IF(VALUE(MID($A4,12,2))<7,VALUE(MID($A4,1,10))-1,VALUE(MID($A4,1,10))) =IF(VALUE(MID($A4,12,2))<7,VALUE((MID($A4,12,2))-7+24),VALUE((MID($A4,12,2))-7)) =MID($A4, 15, 2) =TIME(C4,D4,“00”)
2015-01-01 00:01 +000 =IF(VALUE(MID($A5,12,2))<7,VALUE(MID($A5,1,10))-1,VALUE(MID($A5,1,10))) =IF(VALUE(MID($A5,12,2))<7,VALUE((MID($A5,12,2))-7+24),VALUE((MID($A5,12,2))-7)) =MID($A5, 15, 2) =TIME(C5,D5,“00”)
2015-01-01 01:00 +000 =IF(VALUE(MID($A6,12,2))<7,VALUE(MID($A6,1,10))-1,VALUE(MID($A6,1,10))) =IF(VALUE(MID($A6,12,2))<7,VALUE((MID($A6,12,2))-7+24),VALUE((MID($A6,12,2))-7)) =MID($A6, 15, 2) =TIME(C6,D6,“00”)
2015-01-01 02:58 +000 =IF(VALUE(MID($A7,12,2))<7,VALUE(MID($A7,1,10))-1,VALUE(MID($A7,1,10))) =IF(VALUE(MID($A7,12,2))<7,VALUE((MID($A7,12,2))-7+24),VALUE((MID($A7,12,2))-7)) =MID($A7, 15, 2) =TIME(C7,D7,“00”)
2015-01-01 03:58 +000 =IF(VALUE(MID($A8,12,2))<7,VALUE(MID($A8,1,10))-1,VALUE(MID($A8,1,10))) =IF(VALUE(MID($A8,12,2))<7,VALUE((MID($A8,12,2))-7+24),VALUE((MID($A8,12,2))-7)) =MID($A8, 15, 2) =TIME(C8,D8,“00”)
2015-01-01 04:58 +000 =IF(VALUE(MID($A9,12,2))<7,VALUE(MID($A9,1,10))-1,VALUE(MID($A9,1,10))) =IF(VALUE(MID($A9,12,2))<7,VALUE((MID($A9,12,2))-7+24),VALUE((MID($A9,12,2))-7)) =MID($A9, 15, 2) =TIME(C9,D9,“00”)
2015-01-01 05:58 +000 =IF(VALUE(MID($A10,12,2))<7,VALUE(MID($A10,1,10))-1,VALUE(MID($A10,1,10))) =IF(VALUE(MID($A10,12,2))<7,VALUE((MID($A10,12,2))-7+24),VALUE((MID($A10,12,2))-7)) =MID($A10, 15, 2) =TIME(C10,D10,“00”)
2015-01-01 06:58 +000 =IF(VALUE(MID($A11,12,2))<7,VALUE(MID($A11,1,10))-1,VALUE(MID($A11,1,10))) =IF(VALUE(MID($A11,12,2))<7,VALUE((MID($A11,12,2))-7+24),VALUE((MID($A11,12,2))-7)) =MID($A11, 15, 2) =TIME(C11,D11,“00”)
2015-01-01 07:58 +000 =IF(VALUE(MID($A12,12,2))<7,VALUE(MID($A12,1,10))-1,VALUE(MID($A12,1,10))) =IF(VALUE(MID($A12,12,2))<7,VALUE((MID($A12,12,2))-7+24),VALUE((MID($A12,12,2))-7)) =MID($A12, 15, 2) =TIME(C12,D12,“00”)
2015-01-01 08:58 +000 =IF(VALUE(MID($A13,12,2))<7,VALUE(MID($A13,1,10))-1,VALUE(MID($A13,1,10))) =IF(VALUE(MID($A13,12,2))<7,VALUE((MID($A13,12,2))-7+24),VALUE((MID($A13,12,2))-7)) =MID($A13, 15, 2) =TIME(C13,D13,“00”)
2015-01-01 07:00 +000 =IF(VALUE(MID($A14,12,2))<7,VALUE(MID($A14,1,10))-1,VALUE(MID($A14,1,10))) =IF(VALUE(MID($A14,12,2))<7,VALUE((MID($A14,12,2))-7+24),VALUE((MID($A14,12,2))-7)) =MID($A14, 15, 2) =TIME(C14,D14,“00”)

NOTE:
Tab (only) delimited when opening .csv file in LibreOffice spreadsheets
After opening file in LibreOffice, format column B as MM/DD/YYYY (Format > Cells > Date > MM/DD/YYYY)

Thank you!

[ROSt53]

I recommend to re-write your answer to show the important points.

[w_whalley]

If the cell A1 contains the string 2014-11-02 01:58 +0000 then the formula =left(a1,16)-7/24 will extract the date/time portion of the string and subtract 7 hours (7/24 of one day). This will produce a number that you can format as a date. If you use the user-defined format code YYYY-MM-DD HH:MM:SS you should get what you want. If you wish you can add text to the format code to show the offset from GMT by using the format code YYYY-MM-DD HH:MM:SS "-07:00".