It depends on the marketing effort... and common knowledge will vary greatly - we've had to argue with “professionals” that say it takes 6 months to appear in Google.

In our experience, when executing a proper marketing campaign, new sites take between 2-5 days to show up in Google and 3-7 days to show up in Yahoo.

However, establishing good rankings takes a lot longer. Depending on the marketing effort, sites can start seeing good traffic within the first couple of weeks, but it will take 3-4 months to secure good rankings.