first saturday in a month

I need a formula to find the first saturday in the month. i.e. cell a1
contains the date 2006 02 01, i need to know the first saturday in feb.

This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)

"Barry" wrote:

> I need a formula to find the first saturday in the month. i.e. cell a1
> contains the date 2006 02 01, i need to know the first saturday in feb.

Chip Pearson's web site...
http://www.cpearson.com/excel/datetime.htm#NthDoW
answers this for both a formula and using VBA.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Barry" wrote:

> I need a formula to find the first saturday in the month. i.e. cell a1
> contains the date 2006 02 01, i need to know the first saturday in feb.

On Mon, 6 Feb 2006 13:32:13 -0800, "Barry" <Barry@discussions.microsoft.com>
wrote:

>I need a formula to find the first saturday in the month. i.e. cell a1
>contains the date 2006 02 01, i need to know the first saturday in feb.

If your date in A1 is always the first day of the month, then:

=A1+7-WEEKDAY(A1)

will give you the first Saturday of the month.

If the date in A1 can be any date in the month, the first Saturday of that
month will be given by:

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


--ron

"bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
news:C3FC1F84-47D7-4181-844A-480C55E7CAA4@microsoft.com...
> This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
>
Are you sure? It doesn't seem to work for me for all dates but

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8,1)

seems to work OK

--
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
news:C3FC1F84-47D7-4181-844A-480C55E7CAA4@microsoft.com...
> This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
>
> "Barry" wrote:
>
>> I need a formula to find the first saturday in the month. i.e. cell a1
>> contains the date 2006 02 01, i need to know the first saturday in feb.

Thanks, you're right. =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1,1) gets it.
The logic, BTW: Get to the first day of the month-- a2-day(a2)+1 -- and
make an adjustment based on the weekday that the first of the month falls on
-- 7-weekday(a2-day(a2)+1,1) --. The adjustment adds 0 if the first is
Saturday, 1 on Friday, etc.
--Bruce

"Sandy Mann" wrote:

> "bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
> news:C3FC1F84-47D7-4181-844A-480C55E7CAA4@microsoft.com...
> > This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
> >
> Are you sure? It doesn't seem to work for me for all dates but
>
> =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8,1)
>
> seems to work OK
>
> --
> HTH
>
> Sandy
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "bpeltzer" <bpeltzer.deletethissuffix@cox.net> wrote in message
> news:C3FC1F84-47D7-4181-844A-480C55E7CAA4@microsoft.com...
> > This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
> >
> > "Barry" wrote:
> >
> >> I need a formula to find the first saturday in the month. i.e. cell a1
> >> contains the date 2006 02 01, i need to know the first saturday in feb.
>
>
>
>

Thanks very much ron.

"Ron Rosenfeld" wrote:

> On Mon, 6 Feb 2006 13:32:13 -0800, "Barry" <Barry@discussions.microsoft.com>
> wrote:
>
> >I need a formula to find the first saturday in the month. i.e. cell a1
> >contains the date 2006 02 01, i need to know the first saturday in feb.
>
> If your date in A1 is always the first day of the month, then:
>
> =A1+7-WEEKDAY(A1)
>
> will give you the first Saturday of the month.
>
> If the date in A1 can be any date in the month, the first Saturday of that
> month will be given by:
>
> =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1)
>
>
> --ron
>

Thanks, Ron's suggestion seems to work best.

"bpeltzer" wrote:

> This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
>
> "Barry" wrote:
>
> > I need a formula to find the first saturday in the month. i.e. cell a1
> > contains the date 2006 02 01, i need to know the first saturday in feb.

On Tue, 7 Feb 2006 05:31:23 -0800, "Barry" <Barry@discussions.microsoft.com>
wrote:

>Thanks very much ron.
>

You're welcome. Glad to help.


--ron

Ron's suggestion for > If the date in A1 can be any date in the month, the first Saturday of that
> month will be given by:
>
> =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1)

Isn't that the same as A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1))?

if you add 7 to the first day of the month and then subtract the weekday of
the first day wouldn't that be the same as adding 8 to the first day of the month
and then subtracting the weekday of the second day of the month?