1st Wed, 1st Sat, 1st Sun

[Keyrookie]

Hey all,

I received this formula on this forum to always show the 1st Wed. of
month:

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())-3)


What I now need are the formulas to always find the 1st Sat. and th
1st Sun.

I'm setting up a monthly schedule with 5 weeks and I'm wanting th
dates to automatically show the 1st Wed, Sat, & Sun of the month.
Here's my set-up:

B7 = 1st Wed. (the above formula resides in this cell)
B10 = 1st Sat. (right now the formula in this cell is B7+3)
B13 = 1st Sun. (the formula here is B10+1)

I realize there will be issues with just having the 1st Wed. using th
formula above because dates after Wed (or on Sun) will not be shown.

I have 5 weeks in my schedule & the following week's dates are take
from the auto dates in the first week. For example, B17 (which is We
of the next week) = B13+3, B20 = B17+3, B23 = B20+1. I follow tha
sequence for the remainder of the schedule. As you can see I will hav
problems with only 1 date being auto generated.

Also, if a month starts after a Wed or on Sun., I would like the date
in cell(s) B7 & B10 to be blank since they are from the previou
month.

Thanks in advance for your help,

Keyrooki

 

[Stefi]

See Bob Philips's formula:

Generic formula

=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))

Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.

--

--
Regards!
Stefi



„Keyrookie†ezt írta:

 

[Ron Rosenfeld]

Hey all,

I received this formula on this forum to always show the 1st Wed. of a
month:

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())-3)


What I now need are the formulas to always find the 1st Sat. and the
1st Sun.

The General form of the above formula is:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

where DOW = Day of Week (1=Sunday, 2=Monday, ...)

So you can make the appropriate substitutions.

I'm setting up a monthly schedule with 5 weeks and I'm wanting the
dates to automatically show the 1st Wed, Sat, & Sun of the month.
Here's my set-up:

B7 = 1st Wed. (the above formula resides in this cell)
B10 = 1st Sat. (right now the formula in this cell is B7+3)
B13 = 1st Sun. (the formula here is B10+1)

I realize there will be issues with just having the 1st Wed. using the
formula above because dates after Wed (or on Sun) will not be shown.

I have 5 weeks in my schedule & the following week's dates are taken
from the auto dates in the first week. For example, B17 (which is Wed
of the next week) = B13+3, B20 = B17+3, B23 = B20+1. I follow that
sequence for the remainder of the schedule. As you can see I will have
problems with only 1 date being auto generated.

Also, if a month starts after a Wed or on Sun., I would like the dates
in cell(s) B7 & B10 to be blank since they are from the previous
month.

Since you would still be using B7 and B10 in subsequent calculations, it may be
simpler to use Conditional Formatting to blank these cells.

Use this for the CF formula:

=WEEKDAY(B7+1-DAY(B7),2)>3

and format the font color to be the same as the background color (e.g. white)
when the condition is true.

Thanks in advance for your help,

Keyrookie

--ron

 

[Keyrookie]

Thanks Stefi and Ron, you guys rock!

K

On Fri, 12 Feb 2010 07:16:11 +0000, Keyrookie


The General form of the above formula is:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

where DOW = Day of Week (1=Sunday, 2=Monday, ...)

So you can make the appropriate substitutions.

-

Since you would still be using B7 and B10 in subsequent calculations
it may be
simpler to use Conditional Formatting to blank these cells.

Use this for the CF formula:

=WEEKDAY(B7+1-DAY(B7),2)>3

and format the font color to be the same as the background color (e.g
white)
when the condition is true.


-
--ro

 

[Ron Rosenfeld]

Thanks Stefi and Ron, you guys rock!

Glad to help.
--ron

 

[Stefi]

You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated.

--
Regards!
Stefi



„Keyrookie†ezt írta: