| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to sort XX/XX (the last 2 digits is the year in ascending orde | Excel Discussion (Misc queries) | |||
| How to convert date to financial year format 2006-07 | Excel Worksheet Functions | |||
| How do I convert a month-date format to day number of the year? | Excel Worksheet Functions | |||
| how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
| month & year format in two digits | Excel Discussion (Misc queries) | |||
Hybrid Mode
