ASP--Spreadsheet problem -- sorting by date

Report · Aug 15, 2006

This could be an Excel thing, but I am not ruling out my ASP code.

Using ASP 3.0, I give the user the ability to create a spreadsheet with a
click. In the resulting spreadsheet, I have 2 columns which contain dates,
and which were created by almost exactly the same code, yet they behave
differently when I try to sort.

First, go to http://www.middletree.net/get.htm and right-click and save the
Excel file, then open it.

Note that the dates in both the Open Date and the Last Modified column look
pretty similar. Now, move the cursor so that you aren't accidentally
highlighting an entire column or anything. From the Excel menu, select Data,
then Sort. In the box that comes up, tell it that you want to sort by TSE,
then by Last Modified.

When you do this, you'll see that the TSE part is correct. However, the
LastModified date is out of order. Specifically, there are some 2005 dates
surrounded by 2006 dates.

Interestingly, this problem doesn't seem to occur in the Open Date column. I
have spent a lot of time looking at my ASP code, but I don't see how these
two columns were built any differently.

Ar any rate, how can I make this so that the dates are sorted correctly?

Report · Aug 15, 2006

The problem is in the way that the columns are getting their data types. In
excel if you select and item in the Open Date column, right mouse click and
select format cells, you will see that is is coming through with a Date
format. On the otherhand your Last Modified field has a general format.
Subsequently when it is sorting it starts with the first number in the field
and moves across.

I am not sure how you specifiy the format in a column but that is what is
needed.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Middletree" <middletree@hottttttttmail.com> wrote in message
news:ebtcmd$o6f$1@forums.macromedia.com...
> This could be an Excel thing, but I am not ruling out my ASP code.
>
> Using ASP 3.0, I give the user the ability to create a spreadsheet with a
> click. In the resulting spreadsheet, I have 2 columns which contain dates,
> and which were created by almost exactly the same code, yet they behave
> differently when I try to sort.
>
> First, go to http://www.middletree.net/get.htm and right-click and save
> the
> Excel file, then open it.
>
> Note that the dates in both the Open Date and the Last Modified column
> look
> pretty similar. Now, move the cursor so that you aren't accidentally
> highlighting an entire column or anything. From the Excel menu, select
> Data,
> then Sort. In the box that comes up, tell it that you want to sort by TSE,
> then by Last Modified.
>
> When you do this, you'll see that the TSE part is correct. However, the
> LastModified date is out of order. Specifically, there are some 2005 dates
> surrounded by 2006 dates.
>
> Interestingly, this problem doesn't seem to occur in the Open Date column.
> I
> have spent a lot of time looking at my ASP code, but I don't see how these
> two columns were built any differently.
>
> Ar any rate, how can I make this so that the dates are sorted correctly?
>
>
>
>

Report · Aug 16, 2006

Thanks, but when I format the entire column to date, I still get the same
issue when I try to sort.

"Paul Whitham AdobeCommunityExpert" <design@valleybiz.net> wrote in message
news:ebtrhn$ac0$1@forums.macromedia.com...
> The problem is in the way that the columns are getting their data types.
> In excel if you select and item in the Open Date column, right mouse click
> and select format cells, you will see that is is coming through with a
> Date format. On the otherhand your Last Modified field has a general
> format. Subsequently when it is sorting it starts with the first number in
> the field and moves across.
>
> I am not sure how you specifiy the format in a column but that is what is
> needed.
>
>
> --
> Paul Whitham
> Certified Dreamweaver MX2004 Professional
> Adobe Community Expert - Dreamweaver
>
> Valleybiz Internet Design
> www.valleybiz.net
>
> "Middletree" <middletree@hottttttttmail.com> wrote in message
> news:ebtcmd$o6f$1@forums.macromedia.com...
>> This could be an Excel thing, but I am not ruling out my ASP code.
>>
>> Using ASP 3.0, I give the user the ability to create a spreadsheet with a
>> click. In the resulting spreadsheet, I have 2 columns which contain
>> dates,
>> and which were created by almost exactly the same code, yet they behave
>> differently when I try to sort.
>>
>> First, go to http://www.middletree.net/get.htm and right-click and save
>> the
>> Excel file, then open it.
>>
>> Note that the dates in both the Open Date and the Last Modified column
>> look
>> pretty similar. Now, move the cursor so that you aren't accidentally
>> highlighting an entire column or anything. From the Excel menu, select
>> Data,
>> then Sort. In the box that comes up, tell it that you want to sort by
>> TSE,
>> then by Last Modified.
>>
>> When you do this, you'll see that the TSE part is correct. However, the
>> LastModified date is out of order. Specifically, there are some 2005
>> dates
>> surrounded by 2006 dates.
>>
>> Interestingly, this problem doesn't seem to occur in the Open Date
>> column. I
>> have spent a lot of time looking at my ASP code, but I don't see how
>> these
>> two columns were built any differently.
>>
>> Ar any rate, how can I make this so that the dates are sorted correctly?
>>
>>
>>
>>
>
>

Report · Aug 16, 2006

Turns out you were right, in a way. I had a space hard-coded in the
ASP/HTML, right after the date, so it was sorting it as a string, because it
couldn't reconcile the data in there as being a date.

Report · Aug 16, 2006

Good to hear that you have you sorted. I guess the issue was easier for me
to see because we run the DD/MM/YYYY format and when I opened the sheet the
date field went into our format but the last modified stayed in the
MM/DD/YYYY format.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Middletree" <middletree@hottttttttmail.com> wrote in message
news:ebv5li$pe6$1@forums.macromedia.com...
> Turns out you were right, in a way. I had a space hard-coded in the
> ASP/HTML, right after the date, so it was sorting it as a string, because
> it couldn't reconcile the data in there as being a date.
>