How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Computer Support Specialist
Category: Computer
Satisfied Customers: 38523
Experience:  Over 15 years of consulting to the IT industry
32989067
Type Your Computer Question Here...
Richard is online now

I am using Excel 2003. I have produced a Pivot Table using

Resolved Question:

I am using Excel 2003. I have produced a Pivot Table using daily data. The dates in the row field have appeared as 1/1/10; 1/2/10; 1/3/10 etc. instead of 1/1/10; 2/1/10; 3/1/10. I want to analyse the data by month not by day of the month. How do I change the order of the dates to chronological order?
Submitted: 3 years ago.
Category: Computer
Expert:  Richard replied 3 years ago.
Thank you for your question, my name is XXXXX XXXXX I look forward to assisting you.

Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.

To clarify, you want the date first? Or the month?

Thank you
Customer: replied 3 years ago.

My name isXXXXX am working in the UK, I would prefer the date first then the month.

Expert:  Richard replied 3 years ago.
Thank you Ian

You then use this formula

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

In my formula, the date is in A1

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

I used the Pivot Table Wizard to set up the table. Where do I enter the formula you provided? This is the first Pivot Table I have set up.

Expert:  Richard replied 3 years ago.
You enter it on the data sheet, then base the Pivot Table on the Formula I gave, not the original values, as the formula will display the dates correctly.

Let me know if you have any difficulties please
Customer: replied 3 years ago.

In my data sheet, the dates are in row E. The formula is input into E1 where the heading currently says "Date"?


 


Sorry for being thick.

Expert:  Richard replied 3 years ago.
Its ok.

Provide me your sheet please

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

thank you
Customer: replied 3 years ago.

File ID # XXXXX

Expert:  Richard replied 3 years ago.
Thanks

I did it for you. You see the column Correct Date.

Use this column for your Pivot Table

You can download it from www.wikisend.com under File ID 830772

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you

Customer: replied 3 years ago.

A lot of the dates are showing as #VALUE! - will this affect the Pivot Table before I start with the Pivot Table Wizard?

Expert:  Richard replied 3 years ago.
yes, but for me the dates where showing correct, so I believe its your computers date settings thats the problem.

Can I log on and look please on the connected computer?

It is easier to work with you on this. You can see and control everything I do

Can you please click on my link below.

http://tinyurl.com/9439e

This will download a program to your computer.

Run this program by double clicking it.

You will then see a Code and Password.

Please send that to me in your next message.

Do not close the program, please leave it open.

Make sure to seperate the code numbers with a space eg: 1 2 3 4 5 6 7 8 9

Thank you
Customer: replied 3 years ago.

ID Code 6 9 9 6 2 2 6 5 1


Password XXXXX 4 6 2


 


The numbers in the ID are in 3 batches of 3 numbers separated by one space

Expert:  Richard replied 3 years ago.
connected
Customer: replied 3 years ago.

Using the column "Correct Date" that you originally added, it appears correctly in the Pivot Table, and I will now be able to highlight July as a Block to create a graph for Q3, which was the basis of my original question. On that basis, I am happy you have resolved my problem. Thanks.

Expert:  Richard replied 3 years ago.
ok, when you select the data for the Pivot Table, you only select July Dates, then it will only show July, dont select all the dates Ian.

Thats how you do this.

Let me know if you have any difficulties please
Richard and 2 other Computer Specialists are ready to help you