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?

  • Are these dates entered as text, or as Dates? Could you share a (read-only) link to your spreadsheet, or another spreadsheet demonstrating the problem? It would make it much easier for us to help you. – Vidar S. Ramdal Apr 22 '16 at 7:06

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:

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.