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?
Asked
Active
Viewed 1.6k times
0
-
Do all the datetimes end in `+00:00`? – BigBen Jun 16 '20 at 13:11
-
Did you try to build a solution based on the info you can find here: https://stackoverflow.com/questions/3120915/get-timezone-information-in-vba-excel ? – Luuk Jun 16 '20 at 13:12
-
@BigBen Yes all datetimes end in `+00:00` – mitesh bhanushali Jun 16 '20 at 13:21
2 Answers
3
You could use something like below. It works in 3 parts :
- Substitute "T" from the value with a space.
- Take only left part of value for 19 characters that make up the relevant date and time
- Deduct 7 hours using 7/24 from the time as PDT = UTC - 7 hours
=LEFT(SUBSTITUTE(A2,"T"," "),19) - 7/24
VTi
- 1,309
- 6
- 14
-
-
Change the format pf the cell to date time. Format 》 custom 》 yyyy-mm--dd hh:mm:ss – VTi Jun 17 '20 at 08:10
2
Use SUBSTITUTE, and then subtract 7/24 to subtract 7 hours.
=SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"+00:00","")-7/24
BigBen
- 46,229
- 7
- 24
- 40
-
-
@BigBen after applying above formula i am getting `43997.70829` value – mitesh bhanushali Jun 17 '20 at 03:44
-
That's because you need to apply the date/time format to the cell. Date/times are just *numbers*. – BigBen Jun 17 '20 at 04:31