CODEWITHSUNDEEP
google-sheets
4nando
4nando

Reputation: 13

How to convert timezones in google sheets

So I have a sheet with two columns Time Zone and Time . I want to convert all these times to EST in a new column. Is there a way I can do this either with scripts app or native sheets formula?

Image of Sheet

Upvotes: 1

Views: 97

Answers (1)

player0
player0

Reputation: 1

add Sheet2 and paste in A1:

=UNIQUE(Sheet1!E2:E)

in B column set the diference:

enter image description here

then use formula:

=INDEX(IF(G2:G="",,TEXT(G2:G+IFNA(VLOOKUP(E2:E, Sheet2!A:B, 2, 0)/24), 
 "yyyy-mm-dd h:mm AM/PM")))

enter image description here

demo spreadsheet

Upvotes: 1

Related Questions