colore 1 week end / 3

gosselien

Board Regular
Joined
Jan 15, 2015
Messages
65
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 :biggrin:
Many thanks

Patrick

Excel 2007
AB
2sam/14/03/2015possible to colore with conditional format
3dim/15/03/2015
4lun/16/03/20151 week end / 3 ?
5mar/17/03/2015FAUX
6mer/18/03/2015FAUX
7jeu/19/03/2015FAUX
8ven/20/03/2015FAUX
9sam/21/03/2015VRAI
10dim/22/03/2015VRAI
11lun/23/03/2015FAUX
12mar/24/03/2015FAUX
13mer/25/03/2015FAUX
14jeu/26/03/2015FAUX
15ven/27/03/2015FAUX
16sam/28/03/2015FAUX
17dim/29/03/2015FAUX
18lun/30/03/2015FAUX
19mar/31/03/2015FAUX
20mer/01/04/2015FAUX
21jeu/02/04/2015FAUX
22ven/03/04/2015FAUX
23sam/04/04/2015FAUX
24dim/05/04/2015FAUX
25lun/06/04/2015FAUX
26mar/07/04/2015FAUX
27mer/08/04/2015FAUX
28jeu/09/04/2015FAUX
29ven/10/04/2015FAUX
30sam/11/04/2015VRAI
31dim/12/04/2015VRAI
32lun/13/04/2015FAUX
33mar/14/04/2015FAUX

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Feuil1

Worksheet Formulas
CellFormula
B5=AND(WEEKDAY($A5,2)>5,MOD((INT(MOD(INT(($A5-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B6=AND(WEEKDAY($A6,2)>5,MOD((INT(MOD(INT(($A6-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B7=AND(WEEKDAY($A7,2)>5,MOD((INT(MOD(INT(($A7-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B8=AND(WEEKDAY($A8,2)>5,MOD((INT(MOD(INT(($A8-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B9=AND(WEEKDAY($A9,2)>5,MOD((INT(MOD(INT(($A9-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B10=AND(WEEKDAY($A10,2)>5,MOD((INT(MOD(INT(($A10-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B11=AND(WEEKDAY($A11,2)>5,MOD((INT(MOD(INT(($A11-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B12=AND(WEEKDAY($A12,2)>5,MOD((INT(MOD(INT(($A12-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B13=AND(WEEKDAY($A13,2)>5,MOD((INT(MOD(INT(($A13-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B14=AND(WEEKDAY($A14,2)>5,MOD((INT(MOD(INT(($A14-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B15=AND(WEEKDAY($A15,2)>5,MOD((INT(MOD(INT(($A15-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B16=AND(WEEKDAY($A16,2)>5,MOD((INT(MOD(INT(($A16-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B17=AND(WEEKDAY($A17,2)>5,MOD((INT(MOD(INT(($A17-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B18=AND(WEEKDAY($A18,2)>5,MOD((INT(MOD(INT(($A18-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B19=AND(WEEKDAY($A19,2)>5,MOD((INT(MOD(INT(($A19-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B20=AND(WEEKDAY($A20,2)>5,MOD((INT(MOD(INT(($A20-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B21=AND(WEEKDAY($A21,2)>5,MOD((INT(MOD(INT(($A21-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B22=AND(WEEKDAY($A22,2)>5,MOD((INT(MOD(INT(($A22-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B23=AND(WEEKDAY($A23,2)>5,MOD((INT(MOD(INT(($A23-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B24=AND(WEEKDAY($A24,2)>5,MOD((INT(MOD(INT(($A24-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B25=AND(WEEKDAY($A25,2)>5,MOD((INT(MOD(INT(($A25-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B26=AND(WEEKDAY($A26,2)>5,MOD((INT(MOD(INT(($A26-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B27=AND(WEEKDAY($A27,2)>5,MOD((INT(MOD(INT(($A27-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B28=AND(WEEKDAY($A28,2)>5,MOD((INT(MOD(INT(($A28-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B29=AND(WEEKDAY($A29,2)>5,MOD((INT(MOD(INT(($A29-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B30=AND(WEEKDAY($A30,2)>5,MOD((INT(MOD(INT(($A30-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B31=AND(WEEKDAY($A31,2)>5,MOD((INT(MOD(INT(($A31-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B32=AND(WEEKDAY($A32,2)>5,MOD((INT(MOD(INT(($A32-2)/7)+3/5,52+5/28))+1)/3,1)=0)
B33=AND(WEEKDAY($A33,2)>5,MOD((INT(MOD(INT(($A33-2)/7)+3/5,52+5/28))+1)/3,1)=0)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top