All solutions General QuestionsPIVOT TABLE Expert Solution – General Questions on Pivot Table

Excel - General Question on Pivot Table - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc