Marrying to Formulas

[Steved]

Hello from Steved
Below is the value I have in Cell J18
=IF(MONTH(L13-DAY(L13)+1-WEEKDAY(L13-DAY(L13)+1)+Mtx)
=MONTH(L13),L13-DAY(L13)+1-WEEKDAY(L13-DAY(L13)+1)+Mtx,"")

Below I was given the formula that will find the first
Monday In June, which is a public holiday.

Using the formula above and below is it possible to
combine the both together to find the value first monday
in June.

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

Thankyou.

 

[Bob Phillips]

Steve,

Not clear.

Are these
=IF(MONTH(L13-DAY(L13)+1-WEEKDAY(L13-DAY(L13)+1)+Mtx)
=MONTH(L13),L13-DAY(L13)+1-WEEKDAY(L13-DAY(L13)+1)+Mtx,"")
really two formulas, or is there a typo?

What is Mtx?

What exactly do you want? What doesn't work?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Hello Bob From Steved
1st question No it is not a typo
2nd question Mtx allows me to have a Calendar 7 rows x 6
Cols. In this case instead of down with days it is row wise
ie Sun, Mon, and so on to Saturday. I've put it below.

My objective is now that I have the formula to build this
calendar I would like to go onto the next stage and put in
Public Holidays hence the first Monday in June is a public
holiday, so I was hoping I might be able to either use 2
formulas in the same cell or combine, if not use VBA to
find first monday in June then programing VBA to do a
conditional format to colour text red then light green for
cell background.


Name: Mtx
Menu/Insert/Name/Define
Refers To: ={1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7

=IF(MONTH(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1)+1)+Mtx)=MONTH
(A1),A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1)+1)+Mtx,"")

 

[Bob Phillips]

Steved,

Still don't get it as I don't replicate a calendar.

Where does this formula go, what is in A1, and where is the information that
1st June is a public holiday stored.

Did you look at my previous posted link?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Hello Bob from Steved

bob I am working through the process of formulas,I've got
an answer at my later posts.

Yes I looked at your previous and thinking about what you
replied I decided the way round it was Conditional
Formatting which i did not think of at the time.

Once again thaks.