Find the First Wednesday of a month

[New2excel]

Hi

In my worksheet I would like to type in A1 a month and year (ex. July 2005)

In another cell on page one, I would like the date of the first Wednesday of the month and year in A1 (ex. 7/6). (This us used to calculate other dates on page one of the worksheet.)

On the next page of the worksheet, I would need the second Wednesday of the month and year in A1 (7/13).

On the next page I would need the Third Wednesday, etc. through the end of the month.

Then I need to know how how many pages needed until last Wed. in the month (depends on if the month has 4 weeks, or 5 weeks)

I hope this makes sense.......... :wink:

Thanks for any help.

Suz

 

[meytithveasna]

Thanks barry
Another attempt
=A1+4-WEEKDAY(A1)+IF(WEEKDAY(A1)<4,0,7)

 

[tobyz95]

Thanks for responding, sorry I should have left the last formula off of my question, as I am only using the first two.

So assuming the actual date in A1 is the 1st of the month

=A1+7-WEEKDAY(A1+3)

will give the first Wednesday

If that formula is in A2 then for the next Wednesday you just need

=A2+7

My date is in cell "A80" and is always the 1st of the month, and my weekday in Friday

So in cell "D88" I have:
=A80+8-WEEKDAY(A80+2)

The in cell "E88" I have
=D88+7 etc.

When I got to December where the 1st falls on Friday, the formula gave me 12/08/2017

I finally had to altar it to:
=A80-DAY(A80)+1+7*1-WEEKDAY(A80-DAY(A80)+1-6)
to get the 1st.

I was hoping to fine a formula that would cover both instances without having to altar.

*All of the formulas given below gave me 12/06/2017

 

[barry houdini]

So assuming the actual date in A1 is the 1st of the month

=A1+7-WEEKDAY(A1+3)

will give the first Wednesday

Hello tobyz95

I suggested the formula above for first Wednesday of the month, assuming A1 contains 1st of the month. To change it for other days then all you need to change is the red 3 at the end, 6 will give you first Sunday, 5 first Monday, 4 first Tuesday etc. so for first Friday you just need 1, i.e.

=A1+7-WEEKDAY(A1+1)

The longer formula you quoted will work for any date in A1

 

[tobyz95]

Hello tobyz95

I suggested the formula above for first Wednesday of the month, assuming A1 contains 1st of the month. To change it for other days then all you need to change is the red 3 at the end, 6 will give you first Sunday, 5 first Monday, 4 first Tuesday etc. so for first Friday you just need 1, i.e.

=A1+7-WEEKDAY(A1+1)

The longer formula you quoted will work for any date in A1

As I stated in my last reply, I am already using that, but was looking for something I didn't have to alter if the 1st happens to fall on that day.
Thank You

 

[barry houdini]

=A80+8-WEEKDAY(A80+2)

You said you were using this formula - I don't know where you got that from, it wasn't what I suggested - as you noted, it doesn't always work

If you want the first Friday of the month then you can use this version:

=A80+7-WEEKDAY(A80+1)

With the first of the month in A80 that will give you the first Friday of the month - it works for all months, whether they begin on a Friday or not​

 

[tobyz95]

You said you were using this formula - I don't know where you got that from, it wasn't what I suggested - as you noted, it doesn't always work

If you want the first Friday of the month then you can use this version:

=A80+7-WEEKDAY(A80+1)

With the first of the month in A80 that will give you the first Friday of the month - it works for all months, whether they begin on a Friday or not​

Barry, I am so sorry if I have confused the situation. I cannot find/remember now how I landed on using =A80+8-WEEKDAY(A80+2)
instead of =A80+7-WEEKDAY(A80+1) but it was working until I got to December this year, and it gave me the 8th when of course I needed the 1st.
Thank you so much for your time and expertise, it is greatly appreciated!