IF formula involving dates

tywalker2276

New Member
Joined
Feb 2, 2012
Messages
10
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
I could only work this out in reverse as there is no isdate function

Sheet1

ABC
101/01/2013 01/01/2013
202/01/2013 02/01/2013
3food -
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
CellFormula
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,196,232
Messages
6,014,140
Members
441,805
Latest member
rwm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top