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

  • Thread starter Thread starter Kaaren
  • Start date Start date
K

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.
 
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.
Click to expand...


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