Re: 2nd and 4th Wednesdays formula?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I took a somewhat different approach. I figured that the user would
enter "Year" only A1, and from there want to know all of his 2nd and
4th Wednesdays in a list.

So the first thing is to find which day (between 8 and 14) is the 2nd
Wednesday of January of Year value stored in A1.

After that, a single formula figures out each other date in the list.

The first formula I used (at cell E1) is:
=DATE( A1, 1, 8) + 4 - WEEKDAY( DATE( A1, 1, 8)) + IF( WEEKDAY( DATE(
A1, 1, 1)) > 4, 7, 0)
(The 4 value represents the day of the week we want, Wednesday.
Substituting other values from 1 to 7 would give the other days of the
week, and would re-figure the remaining days in the list to be same day
of week.)

The next formula, used to generate the rest of the list, is:
=IF( DAY( E1 + 14) >= 8, E1 + 14, E1 + 21)

Copy that one down the next 23 rows and you have the entire list of
dates.

Chris

.

Relevant Pages

  • RE: Find the Format Type of a cell
    ... "Chris" wrote: ... > I need to find the format type of a cell in excel. ... Prev by Date: ...
    (microsoft.public.excel.programming)
  • changing cell back color in datasheet
    ... I have a subform datasheet. ... When I click on a cell in a particular column, ... Chris ... Prev by Date: ...
    (microsoft.public.access.formscoding)
  • Re: Display single value for multiple ranges?
    ... > 1) select the cell that contain the formula ... >>> Hi Chris, ... >>> Regards, ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Auto Row format based on single cell
    ... > I'm attempting to create a speadsheet where if a certain value is entered ... > in a cell, then it applies a cell format to the entire row. ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: Format numbers as "Number"
    ... the way down (substituting G8 for A1 in the top cell), ... Upload it to a file-sharing web site and post the URL here. ... to the sites that share a common newsgroup mirror, ...
    (microsoft.public.excel.worksheet.functions)