Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Day of year (Not Julian)
I am using this formula from Chip Pearson's web site.
'====== =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") '====== Which will take a date "04/18/08" and display it as "08109". "08" is the year while "109" is the day of the year. 1. I need to strip the leading zero's 2. How can I make a column convert a date to this format when ever a date is entered? -- Regards XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Day of year (Not Julian)
Stripping the leading zero:
=VALUE(RIGHT(YEAR(A1),2))&TEXT(A1-DATE(YEAR(A1),1,0),"000") I don't understand #2. -- Jim "Rick S." wrote in message ... |I am using this formula from Chip Pearson's web site. | '====== | =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") | '====== | Which will take a date "04/18/08" and display it as "08109". | "08" is the year while "109" is the day of the year. | | 1. I need to strip the leading zero's | 2. How can I make a column convert a date to this format when ever a date is | entered? | | -- | Regards | | XP Pro | Office 2007 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Day of year (Not Julian)
To remove the leading zero:
=RIGHT(YEAR(A1),1)&TEXT(A1-DATE(YEAR(A1),1,0),"000") this will work for all years from 2001 thru 2009. If you are entering normal dates in column A and want them to be converted automatically, then include the following event code in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") If Intersect(Target, r) Is Nothing Then Exit Sub Dim d As Date d = Target.Value t = Chr(34) & d & Chr(34) s1 = "=RIGHT(YEAR(" s2 = "),1)&TEXT(" s3 = "-DATE(YEAR(" s4 = "),1,0),""000"")" s = s1 & t & s2 & t & s3 & t & s4 k = Evaluate(s) Application.EnableEvents = False Target.NumberFormat = "@" Target.Value = k Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200780 "Rick S." wrote: I am using this formula from Chip Pearson's web site. '====== =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") '====== Which will take a date "04/18/08" and display it as "08109". "08" is the year while "109" is the day of the year. 1. I need to strip the leading zero's 2. How can I make a column convert a date to this format when ever a date is entered? -- Regards XP Pro Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Day of year (Not Julian)
Formula works great!
-- Regards XP Pro Office 2007 "Jim Rech" wrote: Stripping the leading zero: =VALUE(RIGHT(YEAR(A1),2))&TEXT(A1-DATE(YEAR(A1),1,0),"000") I don't understand #2. -- Jim "Rick S." wrote in message ... |I am using this formula from Chip Pearson's web site. | '====== | =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") | '====== | Which will take a date "04/18/08" and display it as "08109". | "08" is the year while "109" is the day of the year. | | 1. I need to strip the leading zero's | 2. How can I make a column convert a date to this format when ever a date is | entered? | | -- | Regards | | XP Pro | Office 2007 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Day of year (Not Julian)
Gary, Thanks!
This is where I was stuck, simply did not see how to or should have disected everything and then put it back together. -- Regards XP Pro Office 2007 "Gary''s Student" wrote: To remove the leading zero: =RIGHT(YEAR(A1),1)&TEXT(A1-DATE(YEAR(A1),1,0),"000") this will work for all years from 2001 thru 2009. If you are entering normal dates in column A and want them to be converted automatically, then include the following event code in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") If Intersect(Target, r) Is Nothing Then Exit Sub Dim d As Date d = Target.Value t = Chr(34) & d & Chr(34) s1 = "=RIGHT(YEAR(" s2 = "),1)&TEXT(" s3 = "-DATE(YEAR(" s4 = "),1,0),""000"")" s = s1 & t & s2 & t & s3 & t & s4 k = Evaluate(s) Application.EnableEvents = False Target.NumberFormat = "@" Target.Value = k Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200780 "Rick S." wrote: I am using this formula from Chip Pearson's web site. '====== =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") '====== Which will take a date "04/18/08" and display it as "08109". "08" is the year while "109" is the day of the year. 1. I need to strip the leading zero's 2. How can I make a column convert a date to this format when ever a date is entered? -- Regards XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I crate a function for week of year + year in same cell. | Excel Worksheet Functions | |||
how to convert from julian date to mm/dd/year | Excel Discussion (Misc queries) | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
converting julian day and year to a date? | Excel Worksheet Functions | |||
Excel should support DAYOFYEAR(year,month,day) returns julian dat. | Excel Worksheet Functions |