The ideal length depends on your goal. For team building and relaxation, 2 to 3 days work best. Long enough to really step away from the daily routine, short enough to keep the threshold low.
A single day can work just fine for an active team day. Going for an incentive-style trip with more experience? Then 3 to 5 days are optimal. Always factor in travel time: it counts as part of the experience.