Ask Your Question

Change GMT date and time to GMT minus 7 [closed]

asked 2014-11-05 02:32:49 +0200

pamelar987 gravatar image

updated 2016-03-05 19:48:01 +0200

Alex Kemp gravatar image

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;;Tune in now (7-9 p.m.) for @HipRawkNation;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;;Now Playing The Revelations - How Could You Walk Away (Featuring Tre Williams);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 Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21 Mac OS 10.6.8

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 19:48:26.714550

2 Answers

Sort by » oldest newest most voted

answered 2014-11-05 17:45:30 +0200

w_whalley gravatar image

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".

edit flag offensive delete link more

answered 2014-11-05 08:27:23 +0200

pamelar987 gravatar image

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 ... (more)

edit flag offensive delete link more


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

ROSt52 gravatar imageROSt52 ( 2014-11-05 10:09:24 +0200 )edit

Question Tools

1 follower


Asked: 2014-11-05 02:32:49 +0200

Seen: 577 times

Last updated: Nov 05 '14