sorting dates in pivot table
- Thread starter bfraser
- Start date
Little too busy lately, but I have to revisit this. http://www.speedyshare.com/files/30495932/New_Asset_List-test.xlsm
The link is a new table that has dates and ,due to the newness of the table, blanks in Col E. Data is entered into Cols E,F and G by using the userform assigned to Rect. 2. Hui helped me enormously on this, hence the helper col and formula in Col F. I added a "dummy" date(1/1/00)to remove the blanks and allow me to sort by dates not text. This works for Col G and F, how do I get rid of the blanks in Col E so Excel will allow me to sort by dates?
The link is a new table that has dates and ,due to the newness of the table, blanks in Col E. Data is entered into Cols E,F and G by using the userform assigned to Rect. 2. Hui helped me enormously on this, hence the helper col and formula in Col F. I added a "dummy" date(1/1/00)to remove the blanks and allow me to sort by dates not text. This works for Col G and F, how do I get rid of the blanks in Col E so Excel will allow me to sort by dates?
http://www.speedyshare.com/files/30801061/PM_Tracking_Log_Final-copy.xlsm
New link.
My goal is to be able to sort col E & G by date. Blanks didnt allow that. Added circurlar ref to add dummy date 1/1/00 in Col E and 12/30/00 in Col G. I can sort col G by dates but Col E gives the "Date Filters" option but doesn't acutally filter. Col E uses the userform assigned to Rectangle 2 to enter data.
New link.
My goal is to be able to sort col E & G by date. Blanks didnt allow that. Added circurlar ref to add dummy date 1/1/00 in Col E and 12/30/00 in Col G. I can sort col G by dates but Col E gives the "Date Filters" option but doesn't acutally filter. Col E uses the userform assigned to Rectangle 2 to enter data.
Hi bfraser,
I can't find the pivot table in the workbook.
In the data table in the first worksheet I see where you used a circular reference formula in column E. It evaluates to the "date" 1/0/1900, which is internally represented in Excel as the date serial number 0.
I was able to filter and sort column E on my computer using the drop down list in the column heading. Is that where you are having the problem?
I'm not sure why you need a circular reference. You can just put the constant 0 in those cells, and if formatted as date, it will display as 1/0/1900. I tried this change and still had date filtering options for the column and was still able to sort and filter.
Your formula in column F, by the way, evaluates to "1/1/00" (a text string) under certain conditions--not to a date. Also not that the if you enter a 2-digit year in Excel, it will be evaluated as the year 2000, not the year 1900 that your dummy date in column E is using.
You mention a userform. Does your question relate to it?
Asa
I can't find the pivot table in the workbook.
In the data table in the first worksheet I see where you used a circular reference formula in column E. It evaluates to the "date" 1/0/1900, which is internally represented in Excel as the date serial number 0.
I was able to filter and sort column E on my computer using the drop down list in the column heading. Is that where you are having the problem?
I'm not sure why you need a circular reference. You can just put the constant 0 in those cells, and if formatted as date, it will display as 1/0/1900. I tried this change and still had date filtering options for the column and was still able to sort and filter.
Your formula in column F, by the way, evaluates to "1/1/00" (a text string) under certain conditions--not to a date. Also not that the if you enter a 2-digit year in Excel, it will be evaluated as the year 2000, not the year 1900 that your dummy date in column E is using.
You mention a userform. Does your question relate to it?
Asa