Finding Mondays

[Tanya M]

Windows XP
Excel '07

I have a list of dates-past and future-that will be updated regularly. These
dates will always be a Monday (using Check column and cond. formatting). The
same date could be entered many times.

Sheet Name: DataEntry
(hope this is readable)
Week Start Project Pct Check
11/10/2008 KU-42658 5% Mon
11/10/2008 PKO08007 10% Mon
11/10/2008 PKO08002 20% Mon
11/10/2008 PN08001 2% Mon
11/10/2008 PN08002 5% Mon
11/17/2008 KU-42660 2% Mon
11/17/2008 PKO07987 3% Mon
11/24/2008 PN08003 12% Mon
11/24/2008 PN08004 13% Mon


Sheet Name: ChartData
(will consist of 12 columns (Start Col B) with the first date being the
Monday of the current week). What I'm trying to figure out is how to
'automatically' have the first date be the Monday of the current week (ex,
today is Nov. 7th and I want the first date to show Nov. 3rd). The other 11
dates will just be =B1+7 but I cannot get that first date.

I was think something along the lines of =if(text(today()="Mon",today(),????)

I may be going about this completely wrong, but I'm stuck.

Ultimately, this worksheet will be the basis of a chart. The displayed data
will be a rolling 12 weeks from 'today'. Unfortunately, macros cannot be used.


Thanks in advance.

 

[John C]

You could try:
=TODAY()-(WEEKDAY(TODAY(),2)-1)

 

[FSt1]

hi
try this..
=INT(NOW())-WEEKDAY(NOW(),3)
it will calulate the moday of each week in a month.

regards
FSt1

 

[John C]

You could simplify even more by:
=TODAY()-WEEKDAY(TODAY(),3)

 

[Tanya M]

Thank you both! Each seems to work!

Nice and simple, just the way I like it even though I tend to 'complicate'
things.

 

[Ron Rosenfeld]

Windows XP
Excel '07

I have a list of dates-past and future-that will be updated regularly. These
dates will always be a Monday (using Check column and cond. formatting). The
same date could be entered many times.

Sheet Name: DataEntry
(hope this is readable)
Week Start Project Pct Check
11/10/2008 KU-42658 5% Mon
11/10/2008 PKO08007 10% Mon
11/10/2008 PKO08002 20% Mon
11/10/2008 PN08001 2% Mon
11/10/2008 PN08002 5% Mon
11/17/2008 KU-42660 2% Mon
11/17/2008 PKO07987 3% Mon
11/24/2008 PN08003 12% Mon
11/24/2008 PN08004 13% Mon


Sheet Name: ChartData
(will consist of 12 columns (Start Col B) with the first date being the
Monday of the current week). What I'm trying to figure out is how to
'automatically' have the first date be the Monday of the current week (ex,
today is Nov. 7th and I want the first date to show Nov. 3rd). The other 11
dates will just be =B1+7 but I cannot get that first date.

I was think something along the lines of =if(text(today()="Mon",today(),????)

I may be going about this completely wrong, but I'm stuck.

Ultimately, this worksheet will be the basis of a chart. The displayed data
will be a rolling 12 weeks from 'today'. Unfortunately, macros cannot be used.


Thanks in advance.

On what day does your week end?

Oh well, if Monday is the first day, then Sunday must be the last day and your
"week beginning" date should update every Monday.

The formula for that would be:

=TODAY()+1-WEEKDAY(TODAY()-1)

--ron

 

[FSt1]

you right. that's what i get for quoteing from memory and shooting from the
hip instead of thinking about it first.

oh well
regards
FSt1