colore 1 week end / 3

[gosselien]

Bonjour,

j'ai une erreur dans cette formule et je ne trouve pas pourquoi ...

je dois colorer 1 weed/3 à partir du 14 mars 2015
Many thanks

Patrick

Excel 2007

	A	B
possible to colore with conditional format
1 week end / 3 ?

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFCC00, align: right"]sam/14/03/2015[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFCC00, align: right"]dim/15/03/2015[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]lun/16/03/2015[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]mar/17/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]mer/18/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]jeu/19/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]ven/20/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]sam/21/03/2015[/TD]
[TD="align: right"]VRAI[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]dim/22/03/2015[/TD]
[TD="align: right"]VRAI[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]lun/23/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]mar/24/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]mer/25/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]jeu/26/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]ven/27/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]sam/28/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]dim/29/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]lun/30/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]mar/31/03/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]mer/01/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]jeu/02/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]ven/03/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFCC00, align: right"]sam/04/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFCC00, align: right"]dim/05/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]lun/06/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]mar/07/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]mer/08/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]jeu/09/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]ven/10/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]sam/11/04/2015[/TD]
[TD="align: right"]VRAI[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]dim/12/04/2015[/TD]
[TD="align: right"]VRAI[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]lun/13/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]mar/14/04/2015[/TD]
[TD="align: right"]FAUX[/TD]

</tbody>

Feuil1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B5[/TH]
[TD="align: left"]=AND(WEEKDAY($A5,2)>5,MOD((INT(MOD(INT(($A5-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B6[/TH]
[TD="align: left"]=AND(WEEKDAY($A6,2)>5,MOD((INT(MOD(INT(($A6-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B7[/TH]
[TD="align: left"]=AND(WEEKDAY($A7,2)>5,MOD((INT(MOD(INT(($A7-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B8[/TH]
[TD="align: left"]=AND(WEEKDAY($A8,2)>5,MOD((INT(MOD(INT(($A8-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B9[/TH]
[TD="align: left"]=AND(WEEKDAY($A9,2)>5,MOD((INT(MOD(INT(($A9-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B10[/TH]
[TD="align: left"]=AND(WEEKDAY($A10,2)>5,MOD((INT(MOD(INT(($A10-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B11[/TH]
[TD="align: left"]=AND(WEEKDAY($A11,2)>5,MOD((INT(MOD(INT(($A11-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B12[/TH]
[TD="align: left"]=AND(WEEKDAY($A12,2)>5,MOD((INT(MOD(INT(($A12-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B13[/TH]
[TD="align: left"]=AND(WEEKDAY($A13,2)>5,MOD((INT(MOD(INT(($A13-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B14[/TH]
[TD="align: left"]=AND(WEEKDAY($A14,2)>5,MOD((INT(MOD(INT(($A14-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B15[/TH]
[TD="align: left"]=AND(WEEKDAY($A15,2)>5,MOD((INT(MOD(INT(($A15-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B16[/TH]
[TD="align: left"]=AND(WEEKDAY($A16,2)>5,MOD((INT(MOD(INT(($A16-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B17[/TH]
[TD="align: left"]=AND(WEEKDAY($A17,2)>5,MOD((INT(MOD(INT(($A17-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B18[/TH]
[TD="align: left"]=AND(WEEKDAY($A18,2)>5,MOD((INT(MOD(INT(($A18-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B19[/TH]
[TD="align: left"]=AND(WEEKDAY($A19,2)>5,MOD((INT(MOD(INT(($A19-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B20[/TH]
[TD="align: left"]=AND(WEEKDAY($A20,2)>5,MOD((INT(MOD(INT(($A20-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B21[/TH]
[TD="align: left"]=AND(WEEKDAY($A21,2)>5,MOD((INT(MOD(INT(($A21-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B22[/TH]
[TD="align: left"]=AND(WEEKDAY($A22,2)>5,MOD((INT(MOD(INT(($A22-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B23[/TH]
[TD="align: left"]=AND(WEEKDAY($A23,2)>5,MOD((INT(MOD(INT(($A23-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B24[/TH]
[TD="align: left"]=AND(WEEKDAY($A24,2)>5,MOD((INT(MOD(INT(($A24-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B25[/TH]
[TD="align: left"]=AND(WEEKDAY($A25,2)>5,MOD((INT(MOD(INT(($A25-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B26[/TH]
[TD="align: left"]=AND(WEEKDAY($A26,2)>5,MOD((INT(MOD(INT(($A26-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B27[/TH]
[TD="align: left"]=AND(WEEKDAY($A27,2)>5,MOD((INT(MOD(INT(($A27-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B28[/TH]
[TD="align: left"]=AND(WEEKDAY($A28,2)>5,MOD((INT(MOD(INT(($A28-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B29[/TH]
[TD="align: left"]=AND(WEEKDAY($A29,2)>5,MOD((INT(MOD(INT(($A29-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B30[/TH]
[TD="align: left"]=AND(WEEKDAY($A30,2)>5,MOD((INT(MOD(INT(($A30-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B31[/TH]
[TD="align: left"]=AND(WEEKDAY($A31,2)>5,MOD((INT(MOD(INT(($A31-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B32[/TH]
[TD="align: left"]=AND(WEEKDAY($A32,2)>5,MOD((INT(MOD(INT(($A32-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B33[/TH]
[TD="align: left"]=AND(WEEKDAY($A33,2)>5,MOD((INT(MOD(INT(($A33-2)/7)+3/5,52+5/28))+1)/3,1)=0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gebobs]

I have tried to parse your formula above. From what I can discern, [MOD((INT(MOD(INT(($A33-2)/7)+3/5,52+5/28))+1)/3,1)=0] returns VRAI for any week number that is divisible by three. If that's the case, the equation above can be rewritten as:

=AND(WEEKDAY($A5,2)>5,MOD(WEEKNUM($A5-1),3)=0)

However, this formula seems to be offset by a full week if the formatting above is what you want. In order to account for this, the formula needs to be modified to:

=AND(WEEKDAY($A5,2)>5,MOD(WEEKNUM($A5+1),3)=0)

https://www.dropbox.com/s/58vygui1yw1dh25/Weekend format.xlsx?dl=0

 

[gosselien]

ok but....
i need saturday AND sunday in color , the first saturday is 14 march 2015

Patrick

 

[gebobs]

i need saturday AND sunday in color

As my sheet does.

the first saturday is 14 march 2015

As on my sheet

The sheet I posted on dropbox highlights...

Sat 14 march 2015
Sun 15 march 2015

and then

Sat 4 april 2015
Sun 5 april 2015

Is that not what you want?

 

[gosselien]

Yes... sorry i have a problem with dropbox

Perfect

many thanks

Patrick

 

[gebobs]

Yes... sorry i have a problem with dropbox

Perfect

many thanks

Patrick

Sure thing. Let me know if you need any further help.