date format #18

Hi all,

I have a spreadsheet containing lots of pivot tables. The source data 
contains date formulas in the following ways: 

=IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11

=IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07

As a result, when I try to sort my pivot table data by date, the dates that 
are shown as Nov-07 are recognised as text therefore only allowing me to sort 
by alphabetical order. The dates that are like 2007-11, i am able to sort it 
by the earliest/latest dates.

Is there a way i can get excel to recognise all formats so that i am able to 
sort by how old the dates are?


0
Dal (10)
9/24/2008 10:40:01 AM
excel 39879 articles. 2 followers. Follow

1 Replies
539 Views

Similar Articles

[PageSpeed] 2

Use =IF(FR5="","",FR5)

and format the cells as required.

-- 
__________________________________
HTH

Bob

"Dal" <Dal@discussions.microsoft.com> wrote in message 
news:9D882287-B76E-41FA-811F-833E1A663E28@microsoft.com...
> Hi all,
>
> I have a spreadsheet containing lots of pivot tables. The source data
> contains date formulas in the following ways:
>
> =IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11
>
> =IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07
>
> As a result, when I try to sort my pivot table data by date, the dates 
> that
> are shown as Nov-07 are recognised as text therefore only allowing me to 
> sort
> by alphabetical order. The dates that are like 2007-11, i am able to sort 
> it
> by the earliest/latest dates.
>
> Is there a way i can get excel to recognise all formats so that i am able 
> to
> sort by how old the dates are?
>
> 


0
BobNGs (423)
9/24/2008 11:19:14 AM
Reply:

Similar Artilces:

Date formats
Hi. A text box bound to a DateField has yyyy\.mm\.dd as Format. Data is displayed correctly but while entering it (apparently) reverts to the computer date format, messing everybody's mind. Is there a way to keep entering and showing date format to yyyy\.mm \.dd? Thanks Henry You need to use an input mask. They are described in Acccess Help system. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "H. Martins" wrote: > Hi. > > A text box bound to a DateField has yyyy\.mm\.d...

Last Balanced Date
Does anyone know a quick way to get a list of accounts and the last date they were balanced? I'm finding myself having to select each account and manually check to see if it is up to date. I would think there would be a report of some kind that would display this somewhere, but I can't seem to find one. Any suggestions? Thanks in advance...Skip Skip, I've been thinking of the same exact question ever since I made the upgrade from Money 2004 to 2005. The ability to see the last balanced date across all accounts did exist in the 2004 version, and it was very useful. It...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

.dat format
Can anyone advise why a .xls spreadsheet, when sent as an attachment in an Outlook email, arrives at one destination in a .dat format (and they cannot open this) while it arrives at another location when sent exactly the same way as a .xls spreadsheet? This sometimes happens when sending a Word .doc as an attachment as well. Lewis Shanks Lewis Try sending in "Plain Text" format to all receivers. Some email clients do not handle RTF. Gord Dibben Excel MVP On Mon, 27 Sep 2004 08:02:07 -0700, "Lewis Shanks" <lewis@alexanderscollection.com> wrote: >Can an...

Display text with Unicode format (ex: Chinese) on controls written by VC6.0
Hi all, We can write a project with Debug-Unicode mode and display text with Unicode format? Ex: Chinese or Japanese Idea? Thanks. >We can write a project with Debug-Unicode mode and display text with Unicode >format? Ex: Chinese or Japanese With VC6 you can build a debug (or release) Unicode project that when run on a proper Unicode platform (like XP) should be able to display Chinese or Japanese texts (providing the font you're using has the characters). Dave Tittle bar, menu, button can display text with Unicode format when i build a debug Unicode project? We can use fo...

Personal format of an excel spreadsheet as a template?
I need to use the format of an Excel spreadsheet that I have created as a template for other worksheets in a book and other books. How can I accomplish this, if possible? Create a new workbook with one sheet and format as you wish. File>Save As>File Type>Template(*.xlt) Name it SHEET(Excel will add the .xlt extension. Store this SHEET.XLT in your XLSTART folder. It will be the default worksheet for Insert>Worksheet. Gord Dibben Excel MVP On Wed, 19 Jan 2005 17:29:01 -0800, Nukinhawg <Nukinhawg@discussions.microsoft.com> wrote: >I need to use the format of an E...

sequential date related question
Hi - If for example within cell A3 the date of 09/03/2001 was entered would it be possible in the cell beneath it, A4, to write code tha would break the date down into parts (e.g., Month, Date and Year) an to modify these parts individually? Therefore within cell A4 the code would suggest that it equaled A3 bu that it would have the potentail to modify its parts (e.g., Month, Dat and Year) and not as a whole (e.g., A3+365) Thus what I would like to place in cell A4 is code that would b somehow similar to the following =IF(MONTH(A3)<>12,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3)+1,1,1))...

date formatting changes
I have a VB macro that changes all date formats to dd/mm/yy. This works fine. However, sometimes when I go back into my files at a later date, I find that the dates convert back to dd/mm/yyyy. Why does this happen when I have specifically set the format to dd/mm/yy? There are some custom date formats that don't belong to you. I'm betting that you happened to use the same format that excel uses to tell it to pick up the format from the windows regional settings (under control panel). xl2002 is more honest with the way it deals with dates. (I think it's new with xl2002, but ma...

3 format cell tabs hidden in excel dialog box
When I right click on the cell and go to format cells instead of seeing 6 tabs as usual I see three tabs and the other three are on a lower level and covered by the information in the dialog box. I can only see the very top edge of the tabs. Any ideas what could be causing this? If you're using Excel 97, and making copies of copies of worksheets, you may hit a limit, because of the way the sheets are being numbered in the background. If that's the case, try copying one of the first sheets, instead of the last one. pjbjamison wrote: > When I right click on the cell and g...

How do I restore format cells menu?
I am working in Excel 2002, multi-page workbook. On some pages "format cell" is not operational - neither from the toolbar or by right-clicking mouse. Command is there but produces no result. On other pages in the same book all is well. There are no protected pages, and this is happening only in one particular workbook. Have tried reset to no avail. When copy and paste material to properly functioning page problem is carried as well. Any ideas would be muchly appreciated. ...

Formatting a category axis
is there a way to select a single item in the category axis and bold it You could make a text box with a bold label and position over the category item. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Laurie" <Laurie@discussions.microsoft.com> wrote in message news:55A44D03-38BE-4EBB-83D2-219F8FDA3132@microsoft.com... > is there a way to select a single item in the category axis and bold it Can you rotate a text box? "Bernard Liengme" wrote: > You could make a text box with a bold label and position over the category > ite...

cell formatting problems
I installed Office 2000 onto my desktop before I reformatted the hard drive and everything worked fine. I reformatted and then got a laptop. For some reason certain functions are now refusing to work. Funtions such as: formatting individual cells and colouring individual cells (or lines). I can't change any of the cells properties...the window doesn't come up at all when I do Ctrl+1. Also, when I try to colourize some cells...nothing happens. Please, please help me. I have uninstalled and then reinstalled with no resolution to the problem. I've tried installing different fea...

Is there a particular picture file format type
That works best with Publisher, particularly if you intend to convert the Publisher file to a pdf? Any type that is more efficient file size wise? I'm doing a 4 page newspaper and it's in B&W so I convert the photos to grayscale. Thanks wjhanna wrote: > That works best with Publisher, particularly if you intend to convert the > Publisher file to a pdf? Any type that is more efficient file size wise? I'm > doing a 4 page newspaper and it's in B&W so I convert the photos to grayscale. As far as Publisher is concerned, avoid TIFF and you can't go far wr...

Date and time stamp
I would like to place a time and date in a cell that is only update when the cell that it is linked to is updated in any way. I have trie today() and now() but that updates anytime I do anything anywhere o the spreadsheet. I am using this as a log and as such I want to keep "time stamp" when someone enters their initials. Thanks, Troosh:confused -- Message posted from http://www.ExcelForum.com For a possible solution see http://www.mcgimpsey.com/excel/timestamp.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "troosh >&...

custom date format mmm/yy
Hi, is there any way in a Report to customize the date displayed in the report to be mmm/yy? Or even mmm/yyyy would be better for me than the options Access seems to offer in their reports. All I see there is Short Date, Medium Date, Long date - they all take up so much space in a densely packed report. Thanks, Harold You can use your own formats In a control's format property, enter mmm/yy or mmm/yyyy or mm/yy For a discusson of the formatting characters, put your cursor in the format property and press the F1 key for help. -- John Spencer Access MVP 2002-2005, 2007 Cent...

Date in Report/Query
Hi. I have a report that has worked up until this past week. The query behind the report has the following expression: DateAuth: Format([CredAuthDate],"mm/dd/yy") If I type in 12/31/09....report works fine. Type in anything with "10" as the year and it fails. Any suggestions would be greatly appreciated. If I take off the format for the field it works. Is something different for the year 2010? This is an Access 2003 database. Thanks... I suggest applying criteria to an 'unformatted' field and turn off the display by unchecking the 'Sh...

Celling formatting failing
Hi All, I am having an unsual behaviour and am trying to track the cause. In excel 2000 I am entering the date 17/12/1969 in a cell e.g. B2 then a new date 17/12/2004 below it in C2 then lastly third a date 17/12/2005 in D2. I then highlight the 3 cells which show the date exactly as entered above. I go to Format > Cells and change on the number tab the category from General to date. I then select the desired type (in my case being 17-Dec-69 in the first dates case). The behaviour I see is that the Sample preview above the types field does NOT change to match the selection. Nor does it ...

How can I get yesterdays date in a custom footer &[DATE]-1 does .
How can I get yesterdays date in a custom footer &[DATE]-1 does not work try =today()-1 chuck <chuck@discussions.microsoft.com> wrote in message news:5A51D4C3-07A5-48A8-A594-09F51D4B46A6@microsoft.com... > How can I get yesterdays date in a custom footer &[DATE]-1 does not work Chuck - You will have to manually or programmatically change the displayed date in the footer. There are no page setup codes that will put a different date in a footer. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Date trasfer when importing emails to CRM
When I import old emails into CRM from Outlook they are tagged in CRM with the today's date, the day I imported them. Is there any way to alter this date once in CRM to reflect the actual date of the email? I believe your referring to promoting emails within Outlook. If so, please see this thread: news:5DF7B2F7-B5CE-4FF9-80E3-421D4FE5F500@microsoft.com -- Jason -- This posting is provided "AS IS" with no warranties, and confers no rights. "Eric" <Eric@discussions.microsoft.com> wrote in message news:6CF94DF6-7659-4793-AF6E-439BB480DC29@microsoft.com......

PO Last Receipt Date
If an item has been received on a PO, when you select the PO in Purchase Order Entry, you can go into the Purchasing Item Detail Entry window and see a Last Receipt date for the item that has been received. Is there any way to print this date on the PO printout? Elaine, Easily. This field is, not suprisingly, "Last Receipt Date" and it is the PO Line table. If it isn't already on your report, you should be able to drag it onto the report as long as it's not one of the temp table based reports. And if it is, you may then be able to link the po line table in and th...

Formating issue
The problem i am facing has to do generally with formatting but in order for you ti understand it better i will use a specific example. Let's say that i want to convert a character(number or letter) in superscript. The problem is that after i make this conversion through the font menu, this formatting remains and does not return in the previous non-superscript state-after i press space and the cursor proceeds. Is there a way that this formatting ceases to apply after i move the cursor? Is it necessary every time that-after i finish formatting the character i want- i must go...

outlook 2007
When i try to open an email message with outlook 2007 i get the following message "Cannot open this item. The text formatting command is not available. It may not be installed correctly ......." I have tried uninstalling and reinstalling outlook but the problem is still present, steve Stef, you wrote on Sun, 13 May 2007 13:33:39 +0200: > When i try to open an email message with outlook 2007 i get the following > message "Cannot open this item. The text formatting command is not > available. It may not be installed correctly ......." Creating a new p...

Format cell if cell is zero
I have Excel that show text after Calculating in the cell. I want the cell dosen't show the text before the cell is typing any value. How I do that ?? // sokoban ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If the value is typed in cell B3, and the text is in C3, enter the following formula in cell C3: =IF(B3=0,"","This is the text") or =IF(B3="","","This is the text") When you type in B3, the text wil...

Bizarre Excel Copy/Paste Date Issue
I'm working on 2 xls Excel 2000 files (with all Office Updates and Windows Updates applied on this WinXP PC). I'm copying a date from a cell in A.XLS and pasting it into B.XLS. Both cells are formatted as dates. I copy the 1/1/1999 from A.XLS but when I paste it into B.XLS, it shows up as 1/1/1994. There are NO formulas involved and I have auto-calculation ON. They are not text, they are true date entries. I've never seen anything like this and wouldn't know where to look for help on this except for here. Any suggestions greatly appreciated. -Rob Rob, One work...

date format problem #5
hi all, I am creating a report using a tool called Nvison. it uses a query tool that will return data and populate it to an excel sheet. this query tool has the default date format yyyy/mm/dd which cannot be changed. In my excel file data output ,I want the date to be in the format mm/dd/yyyy. Though i have defined custom date format(mm/dd/yyyy) for my column the data is populated in the format yyyy/mm/dd in the excel sheet.However when i double click on the cell and press enter the format becomes mm/dd/yyyy which is the custom format that i have defined(& the format that i want). can...