Help Convert "MM/DD/YYYY" to "YY:DDD"

[Sam]

Hello,

Please help:

I have a column of dates in the MM/DD/YYYY" format
How can I convert it to the
"YY:DDD" format?

For example, 12/31/2005 would become 05:365 and
01/01/1985 would become 85:01

Thank you!

[Ron Rosenfeld]

On Tue, 24 Jan 2006 02:44:01 -0800, Sam <[email protected]> wrote:

>Hello,
>
>Please help:
>
>I have a column of dates in the MM/DD/YYYY" format
>How can I convert it to the
>"YY:DDD" format?
>
>For example, 12/31/2005 would become 05:365 and
>01/01/1985 would become 85:01
>
>Thank you!


=TEXT(A1,"yy")&":"&TEXT(A1-DATE(YEAR(A1),1,0),"00")


--ron