Google-sheets – Time and Date Edit by GMT Zone

google sheets

I need to edit the date and time format in a Google spreadsheet.
The spreadsheet has 300 rows with the following format in text 2016-04-21 21:28:00 +0000.
Using the find and replace feature I would like to change the +0000 to -7000 which in turn will decrease the time by 7 hrs.

This 2016-04-21 21:28:00 +0000 turn to this 2016-04-14 21:28:00 -7000.

How can I achieve this change?

Best Answer

If these dates are entered in your spreadsheet as text, you will need to use text replacement functions. Luckily, Google Spreadsheets supports find & replace with regular expressions. So:

  1. Click Edit → Find and replace.
  2. Check the Search using regular expressions checkbox
  3. In the Find field, enter (\d+-\d+-\d+\s\d+\:\d+\:\d+)\s(\+0000)
  4. In the Replace with field, enter $1 +0700

Click Replace (or Replace all, if you're feeling lucky).

Explained:
The expression entered into the Find field is a regular expression, which matches 1) a number: \d+, 2) a dash -, 3) another number \d+, and so on. It is a pattern for a datetime in your format: YYYY-MM-DD HH:MM:SS +ZZZZ. Note that the regular expression has parenthesizes around the date/time part, but not around the timezone part.

The expression in the Replace with field says "take the first group (enclosed in parenthesizes), $1, and append -0700 to it".

If you want to learn more about using regular expressions (which can be useful in many situations), there are many good resources on line. These are some good ones:

Related Topic