0

The format of the time I'm working on is this: 12/20/2022 2:00 - 4:00. I'd like to convert this to UTC so -8, i'd hope it'll look like this: 12/19/2022 18:00 - 20:00.

The Google sheet formula =A1+(-8/24) doesn't seem to work since it doesn't recognize the hyphen as a number. I've updated the Format to Number and Date, but get the same error. Is there any other formula or workaround I can try? Thank you.

NoobYubaba
  • 1

1 Answers1

0

The thing here is that they're not a number itself. Considering this is always the format you'll have, you can Try this formula:

=TEXT(DATEVALUE(REGEXEXTRACT(A1,"^(.+) - "))-TIME(8,0,0),"MM/DD/YYYY HH:MM")&" - "&TEXT(TIMEVALUE(REGEXEXTRACT(A1,".* - (.*)"))-TIME(8,0,0),"hh:mm")
Martín
  • 7,849
  • 2
  • 3
  • 13
  • You may want to convert this in a [Named Function](https://support.google.com/docs/answer/12504534) and change A1 and 8 for variables and make it far more flexible! – Martín Nov 19 '22 at 00:49