No value if no value

[Tammie]

I've got this formula for converting calendar dates to Jullian dates, leaving only the 'day' and not the year. That is working perfectly. What I can't figure out now (an it's probably really simple) is for those cells without a date in them yet, for the formula to leave nothing in the cell. Currently when I copy my formula down, I'm getting 525 for the cells with no date in them. Here's the formula I'm using which converts the date to the Jullian Date and then only keeps the last 3 characters on the right (the day of the year):
=(RIGHT(TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000"),3))

I've tried several different ways to get the 'blanks' to remain 'blank' and just can't figure it out. Going to lunch now, it may come to me while I'm out, but please respond if you can help. Like I said, it's probably something simple that I'm missing. Here's one example of what I've tried (putting this at the beginning and bracketing everything together, or not as the case may be)
=IF(is null(A2)=TRUE," ",(RIGHT(TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000"),3)))

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Bob Phillips]

Try

=IF(A2="","",RIGHT(TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000"),3))

 

[Bob Phillips]

BTW, what is this bit supposed to do

TEXT(A2,"yy")&

Truncating to the right-most 3 characters makes tis redundant