display 1st day of each week and week number

  • Thread starter Thread starter Shane
  • Start date Start date
S

Shane

Hi all,

How can I formulate excel so that upon entering a sing year like "2004", it
provide 2 rows such that the first row reports the first day of each week
and the second row reports the week number. eg

1/1/04 8/1/04 etc

Week 1 Week 2 etc



Many thanks!!
 
Hi Frank!

Aw heck, I was hopping to see something a little different
on this one. As I read the op, they want to return the
first day of the week for each week in the year that's
entered in a cell.

So if they enter 2004 as the year, then the cells return
(using Monday as the first day of the week):

1/5/2004 Monday
1/12/2004 Monday
1/19/2004 Monday
...

I've been working on this but can't figure it out just yet!

Biff
 
As far as the week number, you need to follow Franks
advice and figure out just exactly when the week actually
starts. This is very ambiguous!

For the first day of the week, based on Monday being the
first day of the week, and the year being entered in cell
C1, enter this nasty formula in the desired cell:

=VALUE(IF(WEEKDAY(DATE(C1,1,1),2)=1,DATE(C1,1,1),"")&IF
(WEEKDAY(DATE(C1,1,1),2)=2,DATE(C1,1,1)+6,"")&IF(WEEKDAY
(DATE(C1,1,1),2)=3,DATE(C1,1,1)+5,"")&IF(WEEKDAY(DATE
(C1,1,1),2)=4,DATE(C1,1,1)+4,"")&IF(WEEKDAY(DATE(C1,1,1),2)
=5,DATE(C1,1,1)+3,"")&IF(WEEKDAY(DATE(C1,1,1),2)=6,DATE
(C1,1,1)+2,"")&IF(WEEKDAY(DATE(C1,1,1),2)=7,DATE(C1,1,1)
+1,""))

Then you can just add 7 to the adjacent cells. Say the
nasty formula is in A5, in A6 enter:

=A5+7 and just copy across as needed.

Biff
 
Hi Biff
I agree this is very ambigious (I think Norman Harker
posted some solutions for this probelm/issue).
And as I'm a lazy guy I settled for the easy solution :-)
 
OOOPS! Slight correction:
Then you can just add 7 to the adjacent cells. Say the
nasty formula is in A5, in A6 enter:
=A5+7 and just copy across as needed.
Click to expand...

Should be, in C6 enter the formula and copy across.

Biff
 
Hi,
=VALUE(IF(WEEKDAY(DATE(C1,1,1),2)=1,DATE(C1,1,1),"")&IF
(WEEKDAY(DATE(C1,1,1),2)=2,DATE(C1,1,1)+6,"")&IF(WEEKDAY
(DATE(C1,1,1),2)=3,DATE(C1,1,1)+5,"")&IF(WEEKDAY(DATE
(C1,1,1),2)=4,DATE(C1,1,1)+4,"")&IF(WEEKDAY(DATE(C1,1,1),2)
=5,DATE(C1,1,1)+3,"")&IF(WEEKDAY(DATE(C1,1,1),2)=6,DATE
(C1,1,1)+2,"")&IF(WEEKDAY(DATE(C1,1,1),2)=7,DATE(C1,1,1)
+1,""))
Click to expand...

If you're looking for the first Monday of the year:

=DATE(C1,1,8)-WEEKDAY(DATE(C1,1,6))

And then, you increment by 7 as you indicated.

That being said, I'm not sure if it answers the problem of the OP since he must
precise the weeknumbering scheme chosen (ISO, American, specific one).

Regards,

Daniel M.
 
=DATE(C1,1,8)-WEEKDAY(DATE(C1,1,6))

I knew there had to be a more efficient formula! Sweet!

I fully understand the formula, but I'm not following the
logic. Can you explain the logic, please?

Biff
 
Hi,
I fully understand the formula, but I'm not following the
logic. Can you explain the logic, please?
Click to expand...

General formula to find previous Monday to date D
=D-Weekday(D-2)

First Monday of the year
= First Monday of Jan
= The previous Monday to the 8th Jan (so from Jan 1 to Jan 7).
=Jan8th - Weekday(Jan6th)
=DATE(C1,1,8)-WEEKDAY(DATE(C1,1,6))


Regards,

Daniel M.
 
Back
Top