Hi everyone.

I'm fairly new to programming in excel. I have built a spreadsheet
with the help of a friend which uses a form to update a list of
fields, almost like a mini database. Essentially each field in the
form populates through to a single row so that when each field in the
form is completed, it fully completes a row on the sheet.

On the top of each column, there is a sort function. They all work
fine except the date columns. For some reason, the dates just won't
sort properly and to start with were only sorting in order of the
first numerical figure in the date... ie, if the dates were 14/5/08
and 13/06/08, the order would be wrong. On a greater list of examples
it became clear that it was only sorting by the day and not the full
day, month and year.

Someone suggested that I insert another column with a formula to
ascertain the date value of the relevant date column which was a great
idea. However, it's almost like the contents of the original date
cells are not being recognised as being dates, despite the fact that
the cells are set to be in date format. The datevalues keep coming up
as #VALUE. It doesn't matter if I set the format of the cell to text
or date or general or whatever, it just keeps on coming up with that
message. As a result the sort function just isn't working.

Additonally, when I enter a date in to the form it populates through
to the sheet just fine. But when I attempt to sort, the date and month
swap places, so for example a date of the 1st December 2008 would come
out as 12th January 2008. I have no idea why. I've spent forever on
this sheet. Is anyone please able to help me?

I think that both problems are linked. For some reason the contents of
the date columns keeps on being stuffed up. As the data is being
entered through a form there is some programming which I do not fully
understand. If anyone is prepared to have a look at it I can email you
the sheet.

Thanks very much to any helpers.

Cheers
Ross

Hi

It kooks like your form populates your date column with datestrings instead
of dates. Format your date column as General. When all dates there do change
to numbers like 39###, the you have there real dates. When dates there
remain unchanged, you have there text strings instead of real dates.


Arvi Laanemets



wrote in message
...
Hi everyone.

I'm fairly new to programming in excel. I have built a spreadsheet
with the help of a friend which uses a form to update a list of
fields, almost like a mini database. Essentially each field in the
form populates through to a single row so that when each field in the
form is completed, it fully completes a row on the sheet.

On the top of each column, there is a sort function. They all work
fine except the date columns. For some reason, the dates just won't
sort properly and to start with were only sorting in order of the
first numerical figure in the date... ie, if the dates were 14/5/08
and 13/06/08, the order would be wrong. On a greater list of examples
it became clear that it was only sorting by the day and not the full
day, month and year.

Someone suggested that I insert another column with a formula to
ascertain the date value of the relevant date column which was a great
idea. However, it's almost like the contents of the original date
cells are not being recognised as being dates, despite the fact that
the cells are set to be in date format. The datevalues keep coming up
as #VALUE. It doesn't matter if I set the format of the cell to text
or date or general or whatever, it just keeps on coming up with that
message. As a result the sort function just isn't working.

Additonally, when I enter a date in to the form it populates through
to the sheet just fine. But when I attempt to sort, the date and month
swap places, so for example a date of the 1st December 2008 would come
out as 12th January 2008. I have no idea why. I've spent forever on
this sheet. Is anyone please able to help me?

I think that both problems are linked. For some reason the contents of
the date columns keeps on being stuffed up. As the data is being
entered through a form there is some programming which I do not fully
understand. If anyone is prepared to have a look at it I can email you
the sheet.

Thanks very much to any helpers.

Cheers
Ross