How to Display Products in a table format?

Excel date format

• QUESTION: I have reformatted a date field on my spreadsheet to only display the mm/dd/yyyy without the minutes and seconds. But when I pivot on the spreadsheet and bring the filed in it brings in the minutes an seconds. I do not want this. How can I eliminate the minutes and seconds? Thanks. Bill ANSWER: I don't know whichversion you are using. Just take your cursor in the Heading of that (Pivot) Date Field. Just do double click on it Excel 2003:- You will get a pivot table field Dialog box>>Click Number>>Select Date and your desired Date Format. Excel 2007:- You will get Field Settings Dialog box>>Click Number Format>>Select Date and your desired Date Format. For pivot you should not do the formatting to the cells manually, you should set the formatting to the Pivot Field which will make the field data as per the format defined by you. Hope it's clear to you! ---------- FOLLOW-UP ---------- QUESTION: I'm using 2007. I see the field settings box. There are 2 tabs- subtotals & filters and layout&print. I do not see a date format option. What am I doing wrong? Thanks. Bill QUESTION: Hopefully this is an easy one for you. I often copy and paste a report from the internet to2007 that has column of data in an odd date format. The date format from the internet pastes as 'DD/MM/YY' or 'DD/MM/YYYY' (both formats are contained in the column of data and only those two formats exist in the data). When I select the column, and change the format to 'DD-MMM-YY', it only updates fields where the first two digits are 12 or less, and even on those, the month and day are switched. Obviously it's looking at the day as the month and the month as the day. I need a formula that I can use to change the format to a recognizable date so I can sort the data by date. ANSWER: Darrell Can you send me a sample of the file, especially the ones that don't update properly, my email is richard(dot)littlewing(at)gmail(dot)com Richard ---------- FOLLOW-UP ---------- QUESTION: Richard, I sent the file to the email you gave me.

• Answer:

  here are 2 tabs- subtotals & filters and layout&print. I do not see a date format option Excel 2007:- You will get Field Settings Dialog box>>Click Number Format>>Select Date and your desired Date Format. In my previous statement i have mentioned CLICK NUMBER FORMAT, if you click that then you will get the format option. I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. http://www.sendspace.com/file/hykw5t Scroll the mouse button to the bottom of the website and click Download The File Name is: Field Format Clarification.xls Hope it's Clear to you! Darrell In a new column this formula will change all the dates to the correct ones =DATE(2000+RIGHT(E10,2),MID(E10,4,2),LEFT(E10,2)) You can then copy and paste special It will only work on dates that haven't already been formatted to US as I notice that some already have in your sample. I would import the dates as text first then do this step aswill automatically change the dates when imported as real dates. I would also see if the original program can be altered so that it doesn't export as UK or Aus date styles. I have sent you the sample file you sent me with an explanation and the formula. Richard