find the first Monday in June

[Steved]

Hello from Steved

I have a calendar for all year in excel
How do I find The first Monday in June and then change
that cell to reflect it is a holiday ie font to red and
white to background.

Thankyou.

 

[Norman Harker]

Hi Steved!

One formula for the first Monday in June:

Assuming A1 contains a date in the year you want it for:

=DATE(YEAR(A1),6,CHOOSE(WEEKDAY(DATE(YEAR(A1),6,1)),2,1,7,6,5,4,3))

But there are other formulas that are probably more efficient.

As for highlighting it, take a look at conditional formatting. You
should be able to use this formula as the basis for the conditional
format.

 

[Steved]

Thankyou Norman

 

[Daniel.M]

Hi Norman,

Assuming A1 contains a date in the year you want it for:

=DATE(YEAR(A1),6,CHOOSE(WEEKDAY(DATE(YEAR(A1),6,1)),2,1,7,6,5,4,3))

But there are other formulas that are probably more efficient.

Yes. ALL formulas with CHOOSE(WEEKDAY(aDAte),
serie_of_numbers_1_7_in_increment_order) are sub-optimal because WEEKDAY() is
'sui-generis' a modulo function that should provide you with what you need.
Thus, you don't need the CHOOSE() function : a similar CHOOSE(WEEKDAY())
construct can always be abbreviated to the simpler WEEKDAY() version.

In this specific case:
=DATE(YEAR(A1),6,8)-WEEKDAY(DATE(YEAR(A1),6,6))

which means the previous Monday to June 8th.

Regards,

Daniel M.

 

[Norman Harker]

Hi Daniel!

Re: "which means the previous Monday to June 8th"

Now that is an explanation that I'll steal!!

 

[Arvi Laanemets]

Hi

Another way - with 1st of June (or of any month) in A1
=A1+MOD(9-WEEKDAY(A1),7)

And the same with any date of month in A1
=DATE(YEAR(A1),MONTH(A1),1)+MOD(9-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)

 

[Daniel.M]

Arvi,

MOD() is superfluous for the same reason I gave to Norman: WEEKDAY() is already
a kind of modulo.

Another way - with 1st of June (or of any month) in A1
=A1+MOD(9-WEEKDAY(A1),7)
=A1+7-WEEKDAY(A1+5)


And the same with any date of month in A1
=DATE(YEAR(A1),MONTH(A1),1)+MOD(9-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)

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

Regards,

Daniel M.

 

[Daniel.M]

==> Already posted but I can't see it.

Arvi,

MOD() is superfluous for the same reason I gave to Norman: WEEKDAY() is already
a kind of modulo.

Another way - with 1st of June (or of any month) in A1
=A1+MOD(9-WEEKDAY(A1),7)
=A1+7-WEEKDAY(A1+5)


And the same with any date of month in A1
=DATE(YEAR(A1),MONTH(A1),1)+MOD(9-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)

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

Regards,

Daniel M.

 

[Sandy Mann]

Arvi,

Your formulas, which both work, seem to work for me without the WEEKDAY
calls and replacing DATE(YEAR(A1),MONTH(A1),1) with A1-DAY(A1)+1 has even
fewer function calls as in:

=A1-DAY(A1)+1+MOD(9-(A1-DAY(A1)+1),7)

Regards

Sandy

 

[Harlan Grove]

Your formulas, which both work, seem to work for me without the
WEEKDAY calls and replacing DATE(YEAR(A1),MONTH(A1),1) with
A1-DAY(A1)+1 has even fewer function calls as in:

=A1-DAY(A1)+1+MOD(9-(A1-DAY(A1)+1),7)

....

With one big caveat: this fails in the 1904 date system, while formulas
based on WEEKDAY still work.

 

[Daniel.M]

==>Post sent for the 3rd time (as I still don't see it)

Arvi,

MOD() is superfluous for the same reason I gave to Norman: WEEKDAY() is already
a kind of modulo.

Another way - with 1st of June (or of any month) in A1
=A1+MOD(9-WEEKDAY(A1),7)
=A1+7-WEEKDAY(A1+5)


And the same with any date of month in A1
=DATE(YEAR(A1),MONTH(A1),1)+MOD(9-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)

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

Regards,

Daniel M.

 

[Sandy Mann]

With one big caveat: this fails in the 1904 date system, while formulas
based on WEEKDAY still work.

Yes Harlan so you told me before, (and I remembered so only after posting).
As I only ever work in the 1900 date system I keep forgetting that there is
another system..

At least the A1-DAY(A1)+1 seems to work in both systems and has fewer
function calls.

Regards

Sandy

 

[Sandy Mann]

==>Post sent for the 3rd time (as I still don't see it)

For a period there I was logging on and finding zero new posts. Was there a
problem with the MS server? I was thinking perhaps that someone was trying
a denial of service attack because it was 4th of July.
Regards,

Sandy

 

[Peo Sjoblom]

For a period there I was logging on and finding zero new posts. Was there a
problem with the MS server? I was thinking perhaps that someone was trying
a denial of service attack because it was 4th of July.
Regards,

Sandy

Server work for about 2-3 hours


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)