Hi,

Trying to get an equation that would give me the dates of the Weekdays
in the week of Xmas? Any help would be appreciated

rac

Hi!

This will give you the date for the Monday of the week of Xmas:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(WEEKDAY(ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005"))),2)=1,ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005")))))

Or enter 12/25/2005 in a cell, A1:

=MAX(IF(WEEKDAY(ROW(INDIRECT(A1-7&":"&A1)),2)=1,ROW(INDIRECT(A1-7&":"&A1))))

Format cell as DATE.

Now, if you want the dates for the entire week:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+ROW(A1)-1

Copy down.

If you want the dates across a row:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+COLUMN(A1)-1

Copy across.

Biff

"rac" wrote in message
news:31ZBe.45112$up5.31706@lakeread02...
Hi,

Trying to get an equation that would give me the dates of the Weekdays in
the week of Xmas? Any help would be appreciated

rac

Biff wrote:
Hi!

This will give you the date for the Monday of the week of Xmas:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(WEEKDAY(ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005"))),2)=1,ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005")))))

Or enter 12/25/2005 in a cell, A1:

=MAX(IF(WEEKDAY(ROW(INDIRECT(A1-7&":"&A1)),2)=1,ROW(INDIRECT(A1-7&":"&A1))))

Format cell as DATE.

Now, if you want the dates for the entire week:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+ROW(A1)-1

Copy down.

If you want the dates across a row:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+COLUMN(A1)-1

Copy across.

Biff

"rac" wrote in message
news:31ZBe.45112$up5.31706@lakeread02...

Hi,

Trying to get an equation that would give me the dates of the Weekdays in
the week of Xmas? Any help would be appreciated

rac



Thanks Biff!! The 3rd one was exactly what I needed.

RAC

You're welcome. Thanks for the feedback!

Biff

"rac" wrote in message
news:%k4Ce.46753$up5.33129@lakeread02...
Biff wrote:
Hi!

This will give you the date for the Monday of the week of Xmas:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(WEEKDAY(ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005"))),2)=1,ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005")))))

Or enter 12/25/2005 in a cell, A1:

=MAX(IF(WEEKDAY(ROW(INDIRECT(A1-7&":"&A1)),2)=1,ROW(INDIRECT(A1-7&":"&A1))))

Format cell as DATE.

Now, if you want the dates for the entire week:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+ROW(A1)-1

Copy down.

If you want the dates across a row:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+COLUMN(A1)-1

Copy across.

Biff

"rac" wrote in message
news:31ZBe.45112$up5.31706@lakeread02...

Hi,

Trying to get an equation that would give me the dates of the Weekdays in
the week of Xmas? Any help would be appreciated

rac



Thanks Biff!! The 3rd one was exactly what I needed.

RAC

In the spirit of smaller is better, try:

=IF(WEEKDAY(A1)=2,A1,A1-MOD(A1-2,7))

Where A1 contains your Christmas date, the formula will return the Monday of
that week. You can add 1 to this date to get Tuesday, Wednesday ....

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Biff" wrote in message
...
You're welcome. Thanks for the feedback!

Biff

"rac" wrote in message
news:%k4Ce.46753$up5.33129@lakeread02...
Biff wrote:
Hi!

This will give you the date for the Monday of the week of Xmas:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(WEEKDAY(ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005"))),2)=1,ROW(INDIRECT("12/25/2005"-7&":"&DATEVALUE("12/25/2005")))))

Or enter 12/25/2005 in a cell, A1:

=MAX(IF(WEEKDAY(ROW(INDIRECT(A1-7&":"&A1)),2)=1,ROW(INDIRECT(A1-7&":"&A1))))

Format cell as DATE.

Now, if you want the dates for the entire week:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+ROW(A1)-1

Copy down.

If you want the dates across a row:

=MAX(IF(WEEKDAY(ROW(INDIRECT($A$1-7&":"&$A$1)),2)=1,ROW(INDIRECT($A$1-7&":"&$A$1))))+COLUMN(A1)-1

Copy across.

Biff

"rac" wrote in message
news:31ZBe.45112$up5.31706@lakeread02...

Hi,

Trying to get an equation that would give me the dates of the Weekdays
in the week of Xmas? Any help would be appreciated

rac



Thanks Biff!! The 3rd one was exactly what I needed.

RAC