I don't add a lot of custom holidays, but I do move the existing ones around. Mostly to Synch up with City Living Festivals.
I play with two-week seasons, so I like to move Winterfest and New Years' to the first week of winter because where I live, we still have plenty of snow waiting for us after new Year's. I use LittleMissSam's School Holiday tradition to make a Winter Break between the two as well. I've toyed with replacing some of the traditions, but I usually end up bringing it back to the defaults.
Depending on which festival schedule I get, I move Love Day to either the last Saturday in Winter or the First Saturday in Spring to coordinate with the Romance Festival. I drop the Date requirement and replace it with Romantic TV, so that if the weather's bad, my Sims can have a cozy evening at home (or I'm not trying to juggle the Parent's date night with Teen Kid's date night.) I've also replaced Give Flowers with Give Gifts so that the gifts can be more personalized. I'll also add either a Bunny Day or a Flower Day as the next day, but I'm constantly editing what traditions go with it as the family develops, but it usually involves gardening and the Flower Bunny. Egg hunt depends on 1) the age of the kids and 2) if I've already completed the egg collection with this save.
Summer gets the bulk of my custom Holidays, though again I like to coordinate them with City Living Festivals. Being a Geek who likes cons IRL, Summer Geek Con is a holiday. Go to Geek Con (well, travel, but the intended destination is GeekCon), Wear Costumes, Play Games. It's not a day off from work, but it is a day off of school. The other Summer Holiday I add is SummerFest, which is usually just Grill out and Watch Fireworks, so I try to synch it to the Humor and Hijinks festival. That one gets a day off from work. And between the two (plus a few days if needed) is Summer Break.
Fall I'll add Fright Fest, though again the traditions shift a bit depending on the needs of the family. Wear Costumes and Decorate are about the only constants. I'll sometimes remove the Gnomes from Harvestfest and replace it with watch sports in the grand tradition of American Football on Thanksgiving, but otherwise, I leave it alone.