How do I check which cells in column are date format?

M

malycom

Hi

A few weeks ago I located the answer to this but now I can't find it anywhere.

I have a formula that adds 10 years to a date in the cell next to it.

My problem is, if one of the dates is not formatted correctly, the formula
returns an error.

I am sure all I had to do was something along the lines of =(CELL)ISDATE or
something like that. It just said TRUE or FALSE........

I may be miles wrong here but I hope someone can help.

I just need to see if all the cells in the column are formatted correctly.

Kind regards

Malcolm
 
B

Bob Phillips

Malcolm.

A quick look at the CELL function in help would have told you.

=CELL("format",E1)="D1"

is TRUE for a format of d-mmm-yy. See help for other formats supported.
 
D

Dave Peterson

And the format for that cell with the date doesn't matter when you do arithmetic
with it.

If A1 contained the date, then this:

=date(year(a1)+10,month(a1),day(a1))

should add 10 years no matter how that cell is formatted (as long as it's really
a date--not text!)
 
Top