hi,

i need a function to calculate what date would be the 3rd monday in January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg

Try this...

=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6))

The general formula is:

=DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week))

Whe

N = the nth weekday (3 for the 3rd Monday)

day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
hi,

i need a function to calculate what date would be the 3rd monday in
January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg

Left out the critical piece of information!

Try this...
=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6))

A1 holds the year number of interest. For example, 2011.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this...

=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6))

The general formula is:

=DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week))

Whe

N = the nth weekday (3 for the 3rd Monday)

day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
hi,

i need a function to calculate what date would be the 3rd monday in
January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg

Biff,

Never seen that method before, very neat but I bet you meant:-

=DATE(A1,1,21)-WEEKDAY(DATE(A1,1,6))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"T. Valko" wrote:

Try this...

=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6))

The general formula is:

=DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week))

Whe

N = the nth weekday (3 for the 3rd Monday)

day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
hi,

i need a function to calculate what date would be the 3rd monday in
January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg


.

Biff,

Apologies, i understand your formula now.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Biff,

Never seen that method before, very neat but I bet you meant:-

=DATE(A1,1,21)-WEEKDAY(DATE(A1,1,6))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"T. Valko" wrote:

Try this...

=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6))

The general formula is:

=DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week))

Whe

N = the nth weekday (3 for the 3rd Monday)

day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
hi,

i need a function to calculate what date would be the 3rd monday in
January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg


.

Hi,

try this where a1 contains any properly formatted date.

=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),22,21,20,19,18,17,23)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Greg" wrote:

hi,

i need a function to calculate what date would be the 3rd monday in January
for a given year. can this be done w/o arrays?

thanks

--
______
Regards,
Greg