convert day of year to numeric value format year+day in 4 digits

[Kaaren]

I want to convert the day of the year into the format of year then the 3
digit day value - so 9031 would be january 31 2009 - want to have Excel
automatically calculate based on current date.

 

[Teethless mama]

=--(YEAR(A1)-2000&TEXT(DAY(A1),"000"))

 

[Ron Rosenfeld]

I want to convert the day of the year into the format of year then the 3
digit day value - so 9031 would be january 31 2009 - want to have Excel
automatically calculate based on current date.

With current date in A1:

=--(TEXT(A1,"yy") &TEXT(A1-DATE(YEAR(A1)-1,12,31),"000"))

Or you could substitute TODAY() for A1 to automatically update each day:

=--(TEXT(TODAY(),"yy") &TEXT(TODAY()-DATE(YEAR(TODAY())-1,12,31),"000"))

--ron

 

[Shane Devenshire]

Hi,

You can use

If you don't need the number for calculations or

=TEXT(NOW(),"yy")&0&DATEDIF(DATE(YEAR(NOW()),1,1)-1,NOW(),"d")

If you do need the number for calculations.

=--(TEXT(NOW(),"yy")&0&DATEDIF(DATE(YEAR(NOW()),1,1)-1,NOW(),"d"))