how do I create cell validation for format ww yyyy only?

[tcol]

Hi All,
has anybody got a suggestion as to how I can create cell validation for format ww yyyy only (excel 2003)?

regards
tcol

 

[GlennUK]

A custom rule like this might do:

=AND(LEN(A1)=7,MID(A1,3,1)=" ",NOT(ISERROR(VALUE(MID(A1,4,4)))),NOT(ISERROR(VALUE(LEFT(A1,2)))),VALUE(LEFT(A1,2))<=53)

 

[tcol]

cheers indeed, beer genius has no bounds.......just wondering why "FALSE" is displayed in cells that has the custom rule but no WW YYYY has been entered?

slainte
tcol

 

[GlennUK]

Hmmm, no idea at the moment. What formatting do the cells have?

 

[tcol]

cheers Glenn
just general format...........are you playing around with custom rule as everytime I copy it into excel I get a different response?
tcol

 

[GlennUK]

cheers Glenn
just general format...........are you playing around with custom rule as everytime I copy it into excel I get a different response?
tcol

Yes, I used a real cell and real Data Validation to apply the Custom rule ... it works fine for me, the same way for every cell I apply it to. If I apply a formula rule like that to a cell I make sure that the references align with the active cell, of course.

 

[tcol]

cheers Glenn
thanks a million for your help
regards
tcol

 

[Peter_SSs]

Not widely tested, but I think this may achieve a similar result?

=AND(LEN(A1)=7,MID(A1,3,1)=" ",REPLACE(A1,3,1,"")+0<540000)

 

[tcol]

thanks Peter that works also

regards
tcol

 

[tcol]

hi again,
now all I need to do is add in column to calc
difference between planned & actual in weeks ww, any ideas
<TABLE style="WIDTH: 387pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=515 border=0 x:str><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 142pt; mso-width-source: userset; mso-width-alt: 6912" width=189><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><TBODY><TR style="HEIGHT: 54pt; mso-height-source: userset" height=72><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 127pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 54pt; BACKGROUND-COLOR: navy" width=169 height=72>planned week & year completion ww yyyy</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 142pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: navy" width=189>actual week & year completion ww yyyy</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 118pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: navy" width=157>difference between planned & actual in weeks ww</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=169 height=17>48 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=189>52 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 127pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=169 height=17>28 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=189>29 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 118pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=169 height=17>33 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=189>34 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 127pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=169 height=17>19 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=189>29 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 118pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=169 height=17>35 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=189>35 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 127pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=169 height=17>22 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=189>24 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 118pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=157> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=169 height=17>28 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=189>30 2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: silver; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=157> </TD></TR></TBODY></TABLE>

cheers
tcol