|
Xmas Week Dates
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 |
| All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com