You can get indexed into Google within 24 hours. Bing and Yahoo can take a couple of weeks.
If you're talking about "ranking" for certain keywords, people will throw out many opinions. It really depends on how competitive your keywords are.
For a very non-competitive keyword you could easily rank on the first 3 pages of Google in a couple of weeks with proper onsite seo, relevent backlinks, no page errors, etc.
For a medium competitive keyword, it will take you anywhere from 2-12 months to rank in the top three pages. Again, depending on the seo work you put into the site.
For a highly competitive keyword, it will take you anywhere from 6-24 months to rank in the top three pages of Google.
-Theres a lot of "it depends".
-For instance, there are 10 year old sites that have ranked in the top 10 in Google for specific keywords for a long time, and they didn't get there overnight. You're not going to take there spot from 2-3 weeks of seo work, it's just not going to happen.