conditional formating

S

Steved

Hello from Steved

=DATE(YEAR(L13),6,8)-WEEKDAY(DATE(YEAR(L13),6,6))

The above is a formula I use to find The first Monday in
June. What formula would I use to identify 25th Dec as in
both cases this is a holiday.

Thankyou.
 
N

Norman Harker

Hi Steved!

=DATE(YEAR(L13),12,25)

But what happens if, as with 2004, 25-Dec falls on a Saturday or
Sunday. In that case is 27-Dec used as a substitute?
 
S

Steved

Hello Norman

Norman Yes 27-Dec becomes a Substitute and Thankyou
Your formula is only working for years 2006 and 2007
on 2004 it does not nor 2008, 2005 been on a Sunday.

I highlighted the Month Of Dec put your
Formula =DATE(YEAR(L13),12,25)L13=Jun and it will only
operate on years 2006 and 2007.

I have been trying out =DAY(L35)+24, L35=Dec formated
as "mmm" I put it in Conditional Format it will work. I
ran =DAY(L35)+24 on several years using Conditional
Format to change colour and it works.
Any Ideas

Thankyou.
 
N

Norman Harker

Hi Steved!

What date have you got in L13?

Anticipating; Do you have 26-Dec as a holiday as well? This affects
the substitution rules. Usually for 26-Dec falling on a Saturday or
Sunday, the substitute would be the 28th. [n.b if 26th is Sunday, then
25th is Sat and that takes the 27th].
 
S

Steved

Hello From Steved

L13=June L35=Dec

I used L13 for the first Day of the Monday for that month
for conditional formatting and yes I would like to be able
to put rules in conditional formatting to reflect 25th and
26th Dec falling on a Weekend, hence if the 25th fell on a
saturday , the following monday the 27th and tuesday the
28th to be the holiday day's.
Ok using conditional formatting how would I write to
reflect this please so that the cells will change to red
colour.

Thankyou.
-----Original Message-----
Hi Steved!

What date have you got in L13?

Anticipating; Do you have 26-Dec as a holiday as well? This affects
the substitution rules. Usually for 26-Dec falling on a Saturday or
Sunday, the substitute would be the 28th. [n.b if 26th is Sunday, then
25th is Sat and that takes the 27th].

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Hello Norman

Norman Yes 27-Dec becomes a Substitute and Thankyou
Your formula is only working for years 2006 and 2007
on 2004 it does not nor 2008, 2005 been on a Sunday.

I highlighted the Month Of Dec put your
Formula =DATE(YEAR(L13),12,25)L13=Jun and it will only
operate on years 2006 and 2007.

I have been trying out =DAY(L35)+24, L35=Dec formated
as "mmm" I put it in Conditional Format it will work. I
ran =DAY(L35)+24 on several years using Conditional
Format to change colour and it works.
Any Ideas

Thankyou.
Click to expand...


.
Click to expand...
 
N

Norman Harker

Hi Steved!

Since you appear to be constructing a list of holidays, my approach
would be to pick one of the holidays in the list and to use that as
the base. Perhaps New Year is the best to pick for fixing the year.
With the same number of holidays each year, you'll always be
referencing a date in the same relative position.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Steved said:
Hello From Steved

L13=June L35=Dec

I used L13 for the first Day of the Monday for that month
for conditional formatting and yes I would like to be able
to put rules in conditional formatting to reflect 25th and
26th Dec falling on a Weekend, hence if the 25th fell on a
saturday , the following monday the 27th and tuesday the
28th to be the holiday day's.
Ok using conditional formatting how would I write to
reflect this please so that the cells will change to red
colour.

Thankyou.
-----Original Message-----
Hi Steved!

What date have you got in L13?

Anticipating; Do you have 26-Dec as a holiday as well? This affects
the substitution rules. Usually for 26-Dec falling on a Saturday or
Sunday, the substitute would be the 28th. [n.b if 26th is Sunday, then
25th is Sat and that takes the 27th].

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Hello Norman

Norman Yes 27-Dec becomes a Substitute and Thankyou
Your formula is only working for years 2006 and 2007
on 2004 it does not nor 2008, 2005 been on a Sunday.

I highlighted the Month Of Dec put your
Formula =DATE(YEAR(L13),12,25)L13=Jun and it will only
operate on years 2006 and 2007.

I have been trying out =DAY(L35)+24, L35=Dec formated
as "mmm" I put it in Conditional Format it will work. I
ran =DAY(L35)+24 on several years using Conditional
Format to change colour and it works.
Any Ideas

Thankyou.



-----Original Message-----
Hi Steved!

=DATE(YEAR(L13),12,25)

But what happens if, as with 2004, 25-Dec falls on a
Saturday or
Sunday. In that case is 27-Dec used as a substitute?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]


.
Click to expand...


.
Click to expand...
Click to expand...
 
S

Steved

Thankyou.
-----Original Message-----
Hi Steved!

Since you appear to be constructing a list of holidays, my approach
would be to pick one of the holidays in the list and to use that as
the base. Perhaps New Year is the best to pick for fixing the year.
With the same number of holidays each year, you'll always be
referencing a date in the same relative position.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Hello From Steved

L13=June L35=Dec

I used L13 for the first Day of the Monday for that month
for conditional formatting and yes I would like to be able
to put rules in conditional formatting to reflect 25th and
26th Dec falling on a Weekend, hence if the 25th fell on a
saturday , the following monday the 27th and tuesday the
28th to be the holiday day's.
Ok using conditional formatting how would I write to
reflect this please so that the cells will change to red
colour.

Thankyou.
-----Original Message-----
Hi Steved!

What date have you got in L13?

Anticipating; Do you have 26-Dec as a holiday as well? This affects
the substitution rules. Usually for 26-Dec falling on a Saturday or
Sunday, the substitute would be the 28th. [n.b if 26th
Click to expand...
is
Sunday, then
25th is Sat and that takes the 27th].

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Hello Norman

Norman Yes 27-Dec becomes a Substitute and Thankyou
Your formula is only working for years 2006 and 2007
on 2004 it does not nor 2008, 2005 been on a Sunday.

I highlighted the Month Of Dec put your
Formula =DATE(YEAR(L13),12,25)L13=Jun and it will only
operate on years 2006 and 2007.

I have been trying out =DAY(L35)+24, L35=Dec formated
as "mmm" I put it in Conditional Format it will work. I
ran =DAY(L35)+24 on several years using Conditional
Format to change colour and it works.
Any Ideas

Thankyou.



-----Original Message-----
Hi Steved!

=DATE(YEAR(L13),12,25)

But what happens if, as with 2004, 25-Dec falls on a
Saturday or
Sunday. In that case is 27-Dec used as a substitute?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Top