Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dates, Conversions, and Histograms Questions
Some questions.
1. if A1 = 1/5/2005 (date format), is there a function that converts A1 to the day of the year, 5? 2. Suppose the date conversion in 1 is dayofyear(A1). I have a whole column of dates, how can I easily produce a column of =dayofyear(a1) =dayofyear(a2) .... =dayofyear(a20) 3. Suppose I want a count of all the occurrences of events in a week and want to produce a histogram for a 20 week period, is there a way to do it? For example, assuming 1/1/2008 is a Monday, and is the considered the first day of the week, I have this data. Date 1/4/2008 1/6/2008 1/6/2008 1/6/2008 1/7/2008 1/8/2008 1/8/2008 1/10/2008 1/14/2008 .... (2 weeks of no data) 1/29/2008 1/30/2008 1/30/2008 2/10/2008 .... My histogram should start off like this: Week 1: 5 (frequency in first week) Week 2: 4 (second week) Week 3: 0 --this may not be possible under the scheme above Week 4: 0 ditto Week 5: 3 (fifth week) -- Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet "Academic disputes are vicious because so little is at stake." -- Anonymous Web Page: <home.earthlink.net/~mtnviews |
#2
|
|||
|
|||
#1. =A1-DATE(YEAR(A1),1,1)+1
or =A1-DATE(YEAR(A1),1,0) #2. Just insert a new column and drag the formula down using the fill handle. #3. I think I'd insert a helper column to return the Monday for that week: =a2-(WEEKDAY(a2,3)) Then use Data|pivottable to create the summary by week and create the chart from that data. (in later versions of excel, you can create a pivottable and pivotchart at the same time. This actually returns the Monday date. You could use a worksheet function =weeknum(), too. See excel's help for =weeknum() and see Chip Pearson's site if you're concerned about ISO week numbers: http://www.cpearson.com/excel/weeknum.htm "W. Watson" wrote: Some questions. 1. if A1 = 1/5/2005 (date format), is there a function that converts A1 to the day of the year, 5? 2. Suppose the date conversion in 1 is dayofyear(A1). I have a whole column of dates, how can I easily produce a column of =dayofyear(a1) =dayofyear(a2) ... =dayofyear(a20) 3. Suppose I want a count of all the occurrences of events in a week and want to produce a histogram for a 20 week period, is there a way to do it? For example, assuming 1/1/2008 is a Monday, and is the considered the first day of the week, I have this data. Date 1/4/2008 1/6/2008 1/6/2008 1/6/2008 1/7/2008 1/8/2008 1/8/2008 1/10/2008 1/14/2008 ... (2 weeks of no data) 1/29/2008 1/30/2008 1/30/2008 2/10/2008 ... My histogram should start off like this: Week 1: 5 (frequency in first week) Week 2: 4 (second week) Week 3: 0 --this may not be possible under the scheme above Week 4: 0 ditto Week 5: 3 (fifth week) -- Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet "Academic disputes are vicious because so little is at stake." -- Anonymous Web Page: <home.earthlink.net/~mtnviews -- Dave Peterson |
#3
|
|||
|
|||
Dave Peterson wrote:
#1. =A1-DATE(YEAR(A1),1,1)+1 or =A1-DATE(YEAR(A1),1,0) #2. Just insert a new column and drag the formula down using the fill handle. #3. I think I'd insert a helper column to return the Monday for that week: =a2-(WEEKDAY(a2,3)) Then use Data|pivottable to create the summary by week and create the chart from that data. (in later versions of excel, you can create a pivottable and pivotchart at the same time. This actually returns the Monday date. You could use a worksheet function =weeknum(), too. See excel's help for =weeknum() and see Chip Pearson's site if you're concerned about ISO week numbers: http://www.cpearson.com/excel/weeknum.htm "W. Watson" wrote: Some questions. 1. if A1 = 1/5/2005 (date format), is there a function that converts A1 to the day of the year, 5? 2. Suppose the date conversion in 1 is dayofyear(A1). I have a whole column of dates, how can I easily produce a column of =dayofyear(a1) =dayofyear(a2) ... =dayofyear(a20) 3. Suppose I want a count of all the occurrences of events in a week and want to produce a histogram for a 20 week period, is there a way to do it? For example, assuming 1/1/2008 is a Monday, and is the considered the first day of the week, I have this data. Date 1/4/2008 1/6/2008 1/6/2008 1/6/2008 1/7/2008 1/8/2008 1/8/2008 1/10/2008 1/14/2008 ... (2 weeks of no data) 1/29/2008 1/30/2008 1/30/2008 2/10/2008 ... My histogram should start off like this: Week 1: 5 (frequency in first week) Week 2: 4 (second week) Week 3: 0 --this may not be possible under the scheme above Week 4: 0 ditto Week 5: 3 (fifth week) -- Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet "Academic disputes are vicious because so little is at stake." -- Anonymous Web Page: <home.earthlink.net/~mtnviews Good. Thanks for the suggestions. I have a modest sized collection of useful data that I'd like to dispose of somewhat quickly. -- Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet "Academic disputes are vicious because so little is at stake." -- Anonymous Web Page: <home.earthlink.net/~mtnviews |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|