First week of the year
Posted by Greg on April 17, 2001 10:45 AM
How can I display the date of the the first day of the
first full week of the year in a cell?
Posted by Joe on April 17, 2001 11:17 AM
Greg,
Assuming you mean the first Sunday of the year, put the year in cell A1 and the following in A2:
=IF(WEEKDAY(VALUE("1/1/"&A1))=1,VALUE("1/1/"&A1),VALUE("1/1/"&A1)+8-WEEKDAY(VALUE("1/1/"&A1)))
Joe
Posted by Mark W. on April 17, 2001 11:26 AM
Another of a myriad of ways...
=DATE(A1,1,1)+CHOOSE(WEEKDAY(DATE(A1,1,1)),0,6,5,4,3,2,1)
Posted by greg on April 17, 2001 1:24 PM
Could you clarify just a bit....
Mark, thanks for the help. Could you possibly
explain what the arguments in the Choose function are?
(i.e. the 0,6,5,etc.)
Posted by Mark W. on April 17, 2001 1:36 PM
Re: Could you clarify just a bit....
Sure, the 1st argument [in this case, WEEKDAY(DATE(A1,1,1))],
is treated as an index to the remaining arguments. This index
value must be a cardinal number or a #VALUE! error will occur.
Fortunately, the default values produced by the WEEKDAY()
function are between 1 and 7 inclusive. Out of the remaining
arguments the nth value is choosen based on the index value.
CHOOSE() is different from VLOOKUP() or MATCH() in the sense
that it's a direct lookup rather than a sequential examination
of the values.
Posted by Aladin Akyurek on April 17, 2001 1:45 PM
Off topic (Re: Could you clarify just a bit....)
Welcome back from Easter...
Aladin
Posted by Greg on April 17, 2001 1:45 PM
Re: Could you clarify just a bit....
Thanks again. I just have one more question. This is specifying
the first Sunday of the year. I would like it to
return the first Monday. This year, Jan 1 was a monday.
I can get it to return Jan 8 (second monday), and the
earliest date that I can get, by reducing all the numbers
at the end of the string to zeroes, is Jan 2. Any ideas?
Mark, thanks for the help. Could you possibly
Posted by Mark W. on April 17, 2001 1:46 PM
The semantics...
Greg, should have also mentioned that I carefully
selected the 2nd thru 8th arguments so that no days
are added to the 1st date value of the month if its a
Sunday. If the 1st day of the month isn't Sunday then
a sufficient number of days are added to the 1st date
value to return the date value of the 1st Sunday of the
month.
Posted by Mark W. on April 17, 2001 1:48 PM
Re: Off topic (Re: Could you clarify just a bit....)
Thank you!
Posted by Mark W. on April 17, 2001 1:53 PM
Just tweak...
...the CHOOSE() arguments a bit like so:
=DATE(A1,1,1)+CHOOSE(WEEKDAY(DATE(A1,1,1)),1,7,6,5,4,3,2)
