Convert Date to Month / Year 2

[earnestwong]

Hi i have a date field in one column, in another i have the following...

=TEXT(A1,"mmm yy")

This works fine as long as there is data in the field if the field is blank then it just returns Jan 00. How can i get it to not display Jan 00 if the date field is blank?

thankyou,

Erny

 

[HarleyQuinn]

One way would be to check if the cell is blank:

=IF(ISBLANK(A1),"",TEXT(A1,"mmm yy"))

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[earnestwong]

Perfect, your worth a star!

many thanks.

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[chipk]

Using a conditional formula, you could do something like this:

=IF(A1="",#"",TEXT(A1,"mmm yy"))

If the cell is blank, it will fill the destination cell with "N/A", which may or may not work for you. I'm not sure how you would get it to be blank.

 

[chipk]

Whoops, I see someone else already answered this, I walked away and then came back and replied before checking other replies. This formula will also work to give you a blank cell:

=IF(A1="",TEXT("", 0),TEXT(A1,"mmm yy"))

 

[SkipVought]

May I as why you want mmm yy in the column. It is virtually worthless as anything except a display, since you can CALCULATE with it or SORT with it.

Wh not just FORMAT the Date Column as mmm yy

faq68-5827.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}