Yeah, Paul, I'm with Pecoflyer; this is a lot to pile into one formula. It may be possible (except for coloring the cell; that'll probably take conditional formatting), but even if it is you may be better off doing it with multiple "helping columns".

My recommendation is that you start with Pecoflyer's question, because we need to know whether you want to do this with or without VBA; the answers will differ depending on that. And second, that you tackle this a piece at a time. Start with a simple work-hours calculation. Then add in overtime. Then try to figure out how to handle vacations and holidays. And so on, one piece at a time.