Xmas Week Dates

R

rac

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
 
B

Biff

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
 
R

rac

Biff said:
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
Click to expand...
Thanks Biff!! The 3rd one was exactly what I needed.

RAC
 
D

Damon Longworth

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
 
Top