**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a data sheet and need to change dates/times from UTC to PST and then create pivot tables for data analysis.

Solved by C. H. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
26/04/2018 - 07:15

hello?

Excelchat Expert
26/04/2018 - 07:15

Hello

Excelchat Expert
26/04/2018 - 07:15

Welcome to got it pro

Excelchat Expert
26/04/2018 - 07:15

I understand you need to change time from UTC to PST

User
26/04/2018 - 07:16

yes and I have formatting issue

User
26/04/2018 - 07:16

ill explain

Excelchat Expert
26/04/2018 - 07:16

okay, can I see the file?

User
26/04/2018 - 07:16

01 02:52:12
That is the day and time on my workbook

User
26/04/2018 - 07:16

the file is really large so rather not send

Excelchat Expert
26/04/2018 - 07:17

I understand. In that case can you show me some sample data on the preview sheet?

User
26/04/2018 - 07:17

I need to convert to two columns, (which I figured out how to do, problem is I need to convert the time and it may change day yes

User
26/04/2018 - 07:17

one sec

Excelchat Expert
26/04/2018 - 07:17

thanks.

User
26/04/2018 - 07:19

so we need to convert from UTC to PST, then separate day and time

Excelchat Expert
26/04/2018 - 07:19

the first two digits are date, right?

User
26/04/2018 - 07:19

yes day of month, month and year not important

Excelchat Expert
26/04/2018 - 07:20

those values in your file are in date format?

User
26/04/2018 - 07:21

that part of issue, I changed formatting but its not looking right, here let me add some info on what i did

Excelchat Expert
26/04/2018 - 07:21

okay.

User
26/04/2018 - 07:22

i was able to split the cell and then convert B to time

User
26/04/2018 - 07:22

format

User
26/04/2018 - 07:22

used "text to columns" under data tab to do that

Excelchat Expert
26/04/2018 - 07:23

I'm not sure that I'm following you here.

User
26/04/2018 - 07:23

see cell b9

User
26/04/2018 - 07:24

need to change that to PST from UTC time

Excelchat Expert
26/04/2018 - 07:24

hmm, I see that. thanks.

User
26/04/2018 - 07:25

#1, then #2 some days may change day of month if past midnight etc, how do I account for that (its 10,000 line sheet)

User
26/04/2018 - 07:25

cant do by hand

Excelchat Expert
26/04/2018 - 07:25

the difference between the two time zones are 7 hour right?

User
26/04/2018 - 07:25

yes

Excelchat Expert
26/04/2018 - 07:28

If the original data is in dd/mm/yyyyy hh:mm:ss format then =B9-(7/24) this formula will account the change of dates.

User
26/04/2018 - 07:29

its not, it is in D HH:MM:SS

User
26/04/2018 - 07:29

that part of issue and when I tred to change i could not

Excelchat Expert
26/04/2018 - 07:29

I see.

User
26/04/2018 - 07:30

okay, can I convert A9 :'1" to dd/mm/yyyy

Excelchat Expert
26/04/2018 - 07:30

that would solve the issue .

User
26/04/2018 - 07:30

how do I

User
26/04/2018 - 07:30

do that

User
26/04/2018 - 07:31

and make sure b9 and a9 are linked so it changes

Excelchat Expert
26/04/2018 - 07:31

let's see. Please give me a couple of minutes.

User
26/04/2018 - 07:31

k thanks

Excelchat Expert
26/04/2018 - 07:39

I have found a solution. working on it.

User
26/04/2018 - 07:40

okay thanks so much

Excelchat Expert
26/04/2018 - 07:40

But there is a problem with the data.

Excelchat Expert
26/04/2018 - 07:41

Given UTC is 7 hours ahead of PTC, the change of date will always be minus. So, when the date is 1, what should be the result? 30 or 31?

User
26/04/2018 - 07:41

i can overlook that, use 30

Excelchat Expert
26/04/2018 - 07:41

thanks.

User
26/04/2018 - 07:42

this is an excersize that I am doing that has more to deal with ways to shorten delivery times, i just need to be able to say ex( Over ths 10 day period, we had more deliveries between 6pm and 8pm)

Excelchat Expert
26/04/2018 - 07:45

=B9-(7/24) this formula will give you time at PTC

Excelchat Expert
26/04/2018 - 07:45

=IF(OR(AND(right(C9,2)="PM",right(B9,2)="AM"),AND(right(C9,2)="AM",right(B9,2)="PM")),IF(A9-1=0,30,A9),A9)

Excelchat Expert
26/04/2018 - 07:45

this will account for the change in date.

User
26/04/2018 - 07:46

okay qq, if I wanted to change the "30" to 99 for example and I just sub 99 for 30 in the formula

Excelchat Expert
26/04/2018 - 07:46

that's correct.

User
26/04/2018 - 07:47

see a16 and b 16

Excelchat Expert
26/04/2018 - 07:47

hmm

User
26/04/2018 - 07:48

i am getting error using the A2- (7/24)

User
26/04/2018 - 07:48

this was a spreadsheet that was csv, then google doc now excel, I may be in toruble ha

User
26/04/2018 - 07:48

trouble

Excelchat Expert
26/04/2018 - 07:49

A2 is not it correct format.

Excelchat Expert
26/04/2018 - 07:49

The solution I gave after you have converted using text to column.

User
26/04/2018 - 07:50

okay Ill work on it, thanks so much!!

Excelchat Expert
26/04/2018 - 07:51

Thanks for your patience.

Excelchat Expert
26/04/2018 - 07:51

Have a great day ahead.

User
26/04/2018 - 07:51

you too , thanks

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*