Off course, it makes the lore come to life.
There should be a lot's of 'special' days, but most of them should be subtle events like NPC's dressing up for the occasion, special dialog options, discounts, etc...
Big festivals would be awesome, but if the engine can't handle NPC's building stalls and decorating the town it would be silly if a town suddenly transforms out of nowhere (and knowing Bethesda, you getting stuck in a materializing table or something
)
Holidays should be in a separate section in your diary and you should be notified a few days prior to the event to minimize the chance you accidentally miss any, especially if they are quest-related.