Formulas looking at the year in a date field

**snoopy1461** · 12-22-2011, 10:30 AM

I have a formula below that looks in cells E5 and H5

=IF(E5="",YEAR(H5),YEAR(E5))

The problem is that when text is entred into one of the cells the formula shows as #VALUE!.

Is there a way of stopping this?

Regards,

**dilipandey** · 12-22-2011, 10:37 AM

Hi Snoopy,

Use the following formula:-

=IF(OR(E5="",ISTEXT(E5)),YEAR(H5),YEAR(E5))

Regards,
DILIPandey

<click on below 'star' if this helps>

**Fotis1991** · 12-22-2011, 10:40 AM

Hi snoopy

You mean that sometimes you need to type in this cells, or that the user makes wrong type?

In these cells(E5 & H5) you need to have only DAte data??

**snoopy1461** · 12-22-2011, 10:59 AM

Thanks dilipandey, this is just want i needed,

Fotis1991, what i was looking for was to not only type in a date and for the formula to read it, but have text to be able to write in the cell and for the formula to ignore it. Dilipandey has solved this problem with his formula. Thanks for replying to my post though.

Thanks again for your help