CODEWITHSUNDEEP
excelutc
mitesh bhanushali
mitesh bhanushali

Reputation: 87

How to convert UTC to PDT in excel using formula

I have a file from amazon and in that they are providing datetime in UTC format.Below is the example of that :

2020-06-15T23:59:56+00:00

Now I want convert this datetime into PDT format in Excel using formula. Is it possible to do it?

Upvotes: 0

Views: 25503

Answers (2)

BigBen
BigBen

Reputation: 50008

Use SUBSTITUTE, and then subtract 7/24 to subtract 7 hours.

=SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"+00:00","")-7/24

enter image description here

Upvotes: 2

VTi
VTi

Reputation: 1319

You could use something like below. It works in 3 parts :

  1. Substitute "T" from the value with a space.
  2. Take only left part of value for 19 characters that make up the relevant date and time
  3. Deduct 7 hours using 7/24 from the time as PDT = UTC - 7 hours

=LEFT(SUBSTITUTE(A2,"T"," "),19) - 7/24

enter image description here

Upvotes: 3

Related Questions