Compartmentalization was the policy of trying to have everyone know only the amount of information they needed to know to complete their specific task ("need to know"), and no more.

Depending on your task, that information could vary a lot. So for someone working on the weapon design, that could mean a lot of information — enough to know that you were, in fact, building a weapon. Even those people often were not told everything: they might know that the weapon used plutonium, and that the plutonium was being made somewhere ("Site W"), but they didn't know where that somewhere was (e.g., in Washington State). Or they might know that there was a site somewhere out there making polonium for the initiator, but other than some very scarce technical information, they might not know anything else about the site (it was near Dayton, OH).

For people who were only working at the sites, they might not know what their "product" was contributing towards. For the lower-level people, they might not even know what their "product" was: they'd be told that their job was to do X task and not ask questions. So here's how one woman described her job:

It just didn’t make any sense at all. I worked in the laundry at the Monsanto Chemical Company, and counted uniforms. I’ll tell you exactly what I did. The uniforms were first washed, then ironed, all new buttons sewed on and passed to me. I’d hold the uniform up to a special instrument and if I heard a clicking noise — I’d throw it back in to be done all over again. That’s all I did — all day long.

Now today we can pretty easily figured out what her job was — the "clicking noise" was a Geiger counter, so she's checking for radioactive contamination on uniforms, and then cleaning them again if they were contaminated. But she wasn't told even that (and wasn't familiar with a Geiger counter). So she was really in the dark: even about her own job, much less what the plant was doing, much less what the ultimate purpose was.

I only go into this detail to emphasize that for some people, knowledge of what they were ultimately working towards was inherent to their job. For some people, it was very far away from it. Every person except for a very small group at the very top of the project had some level of compartmentalization in their work, but for most it was very vast. It's hard to say how many people, prior to the release of secrecy, knew what the ends of the project really was (making a nuclear weapon), but my estimate is that it is on the order of maybe 5,000 or so (mostly at Los Alamos and the University of Chicago Met Lab, where compartmentalization of this information was the least strict), out of a project where some 500,000 people worked on it in one form or another (so about 1% of the people).

OK, when would everyone have known, more or less? Basically after the announcement of the Hiroshima attack. Following that, the Manhattan Project blitzed American newspapers with articles about the size of the effort, and tried to give credit to the many sites and industries that participated in the project (because they feared that if they didn't, these places would feel compelled to "take" credit and that would result in secrets leaking out). They also worked with the local journalists around the major sites (Oak Ridge, Hanford, Los Alamos) to give them "scoops" that they could use; this was basically an arrangement they cooked up during the war with the journalists, in exchange for their agreement to keep reporting on the sites very quiet until the secret was out.

Several days after the Nagasaki attack, the US government authorized the release of the Smyth Report, the first official history of the making of the atomic bomb. While obviously heavily censored, it gave a reasonably complete "top level" view of how many of the different parts of the project worked together. Many of the Manhattan Project veterans I have talked to clearly remember reading it with awe, suddenly feeling enabled, for the first time, to get a sense of the "whole story." Again, this was not merely because the government wanted to "inform the people" on these things — it was also a means of security precaution, because it was assumed that if the government did not give a "clean" version of the story (and actively threaten harsh penalties for anyone going beyond it), journalists would try to piece it together on their own, and people who felt their contributions were slighted would try to claim credit in another way.