Help differentiating numbers and dates

[berneslai]

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

 

[Rick Rothstein]

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")

 

[Dave Peterson]

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).

 

[Ron Rosenfeld]

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")

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