No value if no value
 

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/tutorials...tom-pagin.aspx

No value if no value
 

Try

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

--
__________________________________
HTH

Bob

<Tammie wrote in message ...
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/tutorials...tom-pagin.aspx

No value if no value
 

BTW, what is this bit supposed to do

TEXT(A2,"yy")&

Truncating to the right-most 3 characters makes tis redundant

--
__________________________________
HTH

Bob

<Tammie wrote in message ...
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/tutorials...tom-pagin.aspx