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

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

Post by S2FhcmV » Mon, 09 Feb 2009 02:18:01


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

Post by VGVldGhsZX » Mon, 09 Feb 2009 02:37:01

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

 
 
 

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

Post by Ron Rosenf » Mon, 09 Feb 2009 04:10:46

On Sat, 7 Feb 2009 09:18:01 -0800, Kaaren < XXXX@XXXXX.COM >




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

Post by U2hhbmUgRG » Mon, 09 Feb 2009 05:37:01

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