Hi to all
I have a spreadsheet with data imported from an external source that
contains five date fields. In one of these the dates look OK but the field
has a general format diplayed as a date, and can be used to perform date
arithmetic. However if I try and perform a numeric custom filter the data is
not recognised. I can correct this by performing a Data / Text to Columns and
selecting a date format, but can anybody explain to me how this anomoly can
be?

Thanks.

Your custom filter should not use the date "10/30/08" instead use
DataValue("10/30/08"). the dates on the worksheet are store as numberr and
comparing again a string date won't work. You need to use DataValue to
convert the string to a number.

"BT" wrote:

Hi to all
I have a spreadsheet with data imported from an external source that
contains five date fields. In one of these the dates look OK but the field
has a general format diplayed as a date, and can be used to perform date
arithmetic. However if I try and perform a numeric custom filter the data is
not recognised. I can correct this by performing a Data / Text to Columns and
selecting a date format, but can anybody explain to me how this anomoly can
be?

Thanks.

Thanks Joel!

"Joel" wrote:

Your custom filter should not use the date "10/30/08" instead use
DataValue("10/30/08"). the dates on the worksheet are store as numberr and
comparing again a string date won't work. You need to use DataValue to
convert the string to a number.

"BT" wrote:

Hi to all
I have a spreadsheet with data imported from an external source that
contains five date fields. In one of these the dates look OK but the field
has a general format diplayed as a date, and can be used to perform date
arithmetic. However if I try and perform a numeric custom filter the data is
not recognised. I can correct this by performing a Data / Text to Columns and
selecting a date format, but can anybody explain to me how this anomoly can
be?

Thanks.

If changing the numberformat of the cells to an unambiguous data format don't
change what you see in the worksheet, then your values aren't really dates.

You may be able to do some stuff to convert to real dates, but I'd be careful.

After converting to real dates, I'd compare the dates in my excel file with the
dates in the original source. You may find that you have dates, but they're not
the dates that they should be.

01/02/03
could be changed to January 2, 2003 in excel
but in the original source, it may represent February 3, 2001



BT wrote:

Hi to all
I have a spreadsheet with data imported from an external source that
contains five date fields. In one of these the dates look OK but the field
has a general format diplayed as a date, and can be used to perform date
arithmetic. However if I try and perform a numeric custom filter the data is
not recognised. I can correct this by performing a Data / Text to Columns and
selecting a date format, but can anybody explain to me how this anomoly can
be?

Thanks.

--

Dave Peterson

Thanks to you too Dave, and I'll heed the warning!

"Dave Peterson" wrote:

If changing the numberformat of the cells to an unambiguous data format don't
change what you see in the worksheet, then your values aren't really dates.

You may be able to do some stuff to convert to real dates, but I'd be careful.

After converting to real dates, I'd compare the dates in my excel file with the
dates in the original source. You may find that you have dates, but they're not
the dates that they should be.

01/02/03
could be changed to January 2, 2003 in excel
but in the original source, it may represent February 3, 2001



BT wrote:

Hi to all
I have a spreadsheet with data imported from an external source that
contains five date fields. In one of these the dates look OK but the field
has a general format diplayed as a date, and can be used to perform date
arithmetic. However if I try and perform a numeric custom filter the data is
not recognised. I can correct this by performing a Data / Text to Columns and
selecting a date format, but can anybody explain to me how this anomoly can
be?

Thanks.

--

Dave Peterson