Celko definitely knows what he's doing and he writes good books.  I think people just get rubbed the wrong way by some of his responses on the newsgroups... but that's neither here nor there...

Aaron Bertrand has some excellent articles on using an Auxiliary Calendar Table over at ASPFAQ:  http://www.aspfaq.com/show.asp?id=2519.  I highly recommend using a date-based calendar table similar to the one he describes, because of the flexibility.  You can use a calendar table like his to mark off regional holidays, easily calculate working days in a time period, etc.  While you could probably do the same type of thing with a numbers table, it won't be as easy or as intuitive.

For Nigel: I prefer to use a single permanent numbers table.  You eliminate the cost of re-creating it over and over (can become pretty substantial if you're re-creating it a lot and inserting a lot of numbers into it) and you can get a potential performance boost by using WITH SCHEMABINDING with UDF's that reference your permanent numbers table.

Thanks