Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating first/last Monday, Tuesday, etc. in a given month in E
Thanks for the replies to my previous question. I would also like to use
Excel to identify the first and last Monday, Tuesday, etc. of a given month by date. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating first/last Monday, Tuesday, etc. in a given month in E
Try something like this:
For A1: (a date) eg 03/01/2006 A2: (a day to find) eg TUE First occurrence of A2 in the month containing A1 C1: =DATE(YEAR(A1),MONTH(A1),1+7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-MATCH(A2,{"SUN","MON","TUE","WED","THU","FRI","SAT "},0))) Last occurrence of A2 in the month containing A1 C2: =DATE(YEAR(A1),MONTH(A1)+1,1+7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-MATCH(A2,{"SUN","MON","TUE","WED","THU","FRI","SAT "},0)))-7 Does that help? *********** Regards, Ron XL2002, WinXP "Rossta" wrote: Thanks for the replies to my previous question. I would also like to use Excel to identify the first and last Monday, Tuesday, etc. of a given month by date. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating first/last Monday, Tuesday, etc. in a given month in E
On Fri, 26 May 2006 11:49:03 -0700, Rossta
wrote: Thanks for the replies to my previous question. I would also like to use Excel to identify the first and last Monday, Tuesday, etc. of a given month by date. Thanks. With a date in some month in A1: First Monday: B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6) Last Monday C1: =B1+28-7*(MONTH(B1)<MONTH(B1+28)) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating first/last Monday, Tuesday, etc. in a given month in E
On Fri, 26 May 2006 20:31:35 -0400, Ron Rosenfeld
wrote: On Fri, 26 May 2006 11:49:03 -0700, Rossta wrote: Thanks for the replies to my previous question. I would also like to use Excel to identify the first and last Monday, Tuesday, etc. of a given month by date. Thanks. With a date in some month in A1: First Monday: B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6) Last Monday C1: =B1+28-7*(MONTH(B1)<MONTH(B1+28)) --ron To expand the above to cover any day of the week, change B1: B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW) where DOW = Day of Week (1=Sun; 2=Mon; etc.) C1 stays the same. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
Function to write Weekday - Monday, Tuesday etc | Excel Discussion (Misc queries) | |||
Calculating Month Average Exch Rate from Exchange Rates Calendar!!! | Excel Worksheet Functions | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Date Calculations | Excel Worksheet Functions |