Hi,

I have been given a 2003 Excel worksheet containing just one column of
data. This column contains dates (dd/mm/yyyy) and values. I have
been asked to differentiate the dates from the values as anything
formatted as a date *is* a date and anything formatted as a number
*is* a number.

However, as most of the values would represent a valid date (i.e.
24769 would be 24/10/1967), I can't find a single formula that can
differentiate between the numbers and dates. I've tried sorting,
string lengths, looking for "/" characters and using text and
datevalue combined.

I thought I'd ask here before I have to look through all 220,000
values manually. Is there a way of looking for a format type?

Thanks,
Bernie

Try this a try (change the cell reference, of course)...

=IF(LEFT(CELL("format",A1))="D","It's a date","It's not a date")

--
Rick (MVP - Excel)


wrote in message
...
Hi,

I have been given a 2003 Excel worksheet containing just one column of
data. This column contains dates (dd/mm/yyyy) and values. I have
been asked to differentiate the dates from the values as anything
formatted as a date *is* a date and anything formatted as a number
*is* a number.

However, as most of the values would represent a valid date (i.e.
24769 would be 24/10/1967), I can't find a single formula that can
differentiate between the numbers and dates. I've tried sorting,
string lengths, looking for "/" characters and using text and
datevalue combined.

I thought I'd ask here before I have to look through all 220,000
values manually. Is there a way of looking for a format type?

Thanks,
Bernie

Just to add to Rick's suggestion:
=if(and(isnumber(a1),LEFT(CELL("format",A1))="D"), "Date","not a date")

Be aware that this could give a wrong result if the format is changed to a date
(or from a date).

Rick Rothstein wrote:

Try this a try (change the cell reference, of course)...

=IF(LEFT(CELL("format",A1))="D","It's a date","It's not a date")

--
Rick (MVP - Excel)

wrote in message
...
Hi,

I have been given a 2003 Excel worksheet containing just one column of
data. This column contains dates (dd/mm/yyyy) and values. I have
been asked to differentiate the dates from the values as anything
formatted as a date *is* a date and anything formatted as a number
*is* a number.

However, as most of the values would represent a valid date (i.e.
24769 would be 24/10/1967), I can't find a single formula that can
differentiate between the numbers and dates. I've tried sorting,
string lengths, looking for "/" characters and using text and
datevalue combined.

I thought I'd ask here before I have to look through all 220,000
values manually. Is there a way of looking for a format type?

Thanks,
Bernie

--

Dave Peterson

On Sun, 10 May 2009 20:35:08 -0400, "Rick Rothstein"
wrote:

Try this a try (change the cell reference, of course)...

=IF(LEFT(CELL("format",A1))="D","It's a date","It's not a date")

--
Rick (MVP - Excel)

2 comments:

1. The OP should be aware that changing the format does not force a
recalculation of the formula.

2. Some "date" formats give a result of G. For example, ddd dd/mm/yyyy
--ron