It was used in World War II:

The USS Guam, an Alaska class Cruiser, was painted in such a scheme. As was the USS Louisville, a Northampton class Cruiser, and the USS Miami, a Cleveland class Light Cruiser.

As far as the effectiveness is concerned that's harder to determine. The point wasn't to hide the ship, but to make it hard to determine range effectively. It's supposed to increase the odds of the opponent missing the first volley not hide anything. In the First World War the idea was to cause the submarine to miss with the first rounds after surfacing allowing escorts to counter. In the Second World War American dazzle camouflage was employed to fake out kamikazes, in fact a number of designs featured "false bows".

That being said, there was something of a question as to whether or not it was effective at all. The original British Admiralty studies were inconclusive, but adoption of the paint jobs correlated to a reduction of losses. This may have been due to the development of new tactics instead, but the correlation was sufficient to convince a number of naval leaders of its effectiveness. The adoption of different methods of range-finding and the introduction of aids that didn't depend on object texturing or comparing objects against the horizon greatly reduced the effective of the style of camouflage as time went on.

There was a study that addressed this more comprehensively. You can find it here. It determined that speed and changes in direction are the defining features. At a sufficiently high speed combined with a change in direction people consistently aim behind their intended target. This is of dubious benefit when discussing lumbering carriers or cargo ships, but might be effective when discussing a destroyer or light cruiser conducting evasive maneuvering.