Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Of course.

https://www.dropbox.com/s/ximiuz11yl...20Progress.xls

I don't know how to attach files, so here is a link to the file shared on dropbox.

This is the rota I am working on so far. As it stands, it works fine, but needs a lot of manual editing for holidays and meetings.

You can see that putting an H in Ryan R's day gives a value error (of course), and underneath with Laura I attempted to write formulae for what I wanted to do with limited knowledge, and trying to literally spell out what I want it to do as I would speak it. It didn't work.

So for example, all the codes work to get total hours, excluding breaks etc, including NI Tax and so on. However, if, in Laura's case, I put an H to signify a holiday, I want it to count towards the staff's total hours, but then separate it at the bottom right, between total working (Trading) and total holiday (Non-Trading) hours.

Any ideas?

PS. Sheet 2 has National Insurance calculations, Sheet 1 will be, but is only draft at the moment, holiday day/hours contracts.