I've come to some tentative conclusion based on the last game I played - Deity, Standard, Continents, France, Space Victory. Keep this in mind - my time frames might be a little off if you use another civ.
I built Workshops in about half of my cities overall. I also built Statue of Liberty, so keep that in mind too - the value of the engineer slot is lower without it, possibly changing the outcome of close decisions. But with SoL, the value of unemployed citizens relative to engineers is higher than without it, so there's clearly several factors at play.
I had three types of cities:
1. Spaceship part cities (high production)
2. Moderate production but not core cities
3. Low production cities
For 1, some time after Universities and second wave settling was a good time. They helped Factories and Windmills go up faster, and the engineer slot was useful - large cities run out of production tiles eventually, and they beat unemployed citizens by 1H/turn (over a long enough timeframe, this can be worth 100 raw hammers or more). I came out ahead on raw hammers, and opportunity cost is marginal considering it's ahead of other production-boosting buildings without direct effects. I consider this a good spot for Workshops. However, somewhat marginal in cities where you intend to purchase a Factory or build a Wonder - in this case, it contributes to less hammers and should be evaluated more strictly.
For 2, after Universities as well (which was later than the Universities for 1). Factories and Windmills aren't going up here, so there's an opportunity cost involved, depending on the cost and benefit of the next building. I built Workshops where Public Schools were not yet available, and would not be for the time for the Workshop to finish (roughly). After Public Schools, usually a Market and Bank follows (to switch to merchants when science goals were complete), which is enough hammers to come out well ahead, even without considering the specialist slot (quite helpful, as these moderate production cities only had a couple good production tiles). Apply this logic as well in highly productive cities in non-space games.
For 3, there was a dilemma. These cities weren't making Public Schools, so Markets/Banks need to be built ASAP for these cities to have any use late game. These cities usually have excess food and gold, so they can be big and unproductive, running science specialists and working trade posted tiles or using unemployed citizens. Since the Workshops wouldn't break even, I just ran tons of unemployed citizens (1F2H with SoL) to finish the Market and Bank, then switched over science specialists to merchants and let loose the unemployed citizens on TP tiles.
The haziest spot is moderate production cities where after Universities, a good building you definitely want is available (e.g. Circus, Public School, Monastery, Mint, or a good UB). In this case, you're probably better off with the building.
tl;dr - build Workshop if (remember: the value of the engineer slot is a modifier too on these conclusions!):
1. No other buildings are very valuable but you'll build 500 hammers worth later (only seems to happen after Universities).
2. You're setting up a spaceship part city and intend to manually build the Factory and Windmill.
3. You're immediately going to build a 500+ hammer building.