IF formula involving dates

[tywalker2276]

Hello all,

I am looking to write a formula that will display the end year of a date or simply list a "-" if anything other than a date is listed. For example, column E of my spreadsheet lists dates ie.. 06/01/13, 7/30/13 etc... but it also has empty cells as well as some text in the cells. In column Q, I would like to write the formula that would show what the corresponding year is in column E if a date is listed. If no date is listed or if there is text in that column, I would simply like a hyphen to be shown in column Q.

Any ideas?

Thanks everyone!

Tyler

 

[Dryver14]

I could only work this out in reverse as there is no isdate function

Sheet1

	A	B	C
1	01/01/2013		01/01/2013
2	02/01/2013		02/01/2013
3	food		-
4			-

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C1 =IF(OR(ISBLANK(A1),ISTEXT(A1)),"-",A1) C2 =IF(OR(ISBLANK(A2),ISTEXT(A2)),"-",A2) C3 =IF(OR(ISBLANK(A3),ISTEXT(A3)),"-",A3) C4 =IF(OR(ISBLANK(A4),ISTEXT(A4)),"-",A4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[tywalker2276]

That worked perfectly! The only thing I added was as follows:

=IF(OR(ISBLANK(A1),ISTEXT(A1)),"-",YEAR(A1))

This way I get the end year to appear!