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.
