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

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.

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

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


"Kaaren" wrote:

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.

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

On Sat, 7 Feb 2009 09:18:01 -0800, Kaaren
wrote:

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

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

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"))


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kaaren" wrote:

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.