Yip skated in on Jimmy Howard's right side and roofed a shot over the Detroit netminder, capping a Colorado comeback that saw the Avs rally from 3-1 and 4-3 deficits.
Ryan O'Reilly's tally with 49 seconds left in the second period pulled the Avs to within 3-2 and Daniel Winnik tied it early the third on an assist from Adam Foote.
When Patrick Eaves scored on a shorthanded goal to put Detroit up 4-3 about 12 minutes into the third, it looked like the Red Wings were set to start the season with three straight wins.
But David Jones' second goal of the game with 8 minutes to go forged a 4-4 tie that carried through a scoreless overtime.
Peter Budaj stopped 24-of-28 shots and Jones added an assist to finish with three points.
Johan Franzen scored twice and Todd Bertuzzi added a pair of assists for the Red Wings.