Dates, Conversions, and Histograms Questions

[W. Watson]

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]

#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]

#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

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>