South Wind's Prayer - Temples offer free healing, which could lead to NPCs flocking to shrines and temples and religious facilities when they're not otherwise occupied. Heart's Day, when Inns offer free beds for the night. This would enable travelling / merchant NPCs to congregate in and around Inns, and allow the player free boarding. Jester's Day - I'm envisioning a lot of bewitched / naked Nords wandering around. My final example would be Merchants's Festival, in which all shops save Mages Guild / equivalents would offer sale reductions. NPCs could cluster in the shops, and stock could maybe 'sell out' by a preset time of the day.
These are just a few examples of generic holidays that are celebrated all over Tamriel. Skyrim specific celebrations could also created, in order to lend atmosphere to the local setting and culture. I'm sure there must be some Skyrim and Nord specific holidays somewhere. At any rate, for a list of generic Tamerilic holidays, as well as the holidays of the provinces of Hammerfall, High Rock and other regional settlements, http://www.imperial-library.info/content/calendar-tamriel
Personally, I am in favour of such things being implemented, and believe it could give a real sense of immersion. For example, players may actually pay attention to the calendar, in order to track the passage of days. Currently the calendar is a mostly ignored gimmick, which isn't overly important. These festivals would lend depth to the game culture, as well as provide benefits to the player on specific days. Perhaps fliers (seasonal equivalent of the Black Horse Courier, maybe?) or rumours could be implemented in the week-long run up to the holiday to let the player know of their proximity. The newspaper would be more practical, with maybe a generic dialogue option stating, "Oh, I am looking forward to the holiday. It's always good every year."