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

Hello everyone and anyone that can help.

I'm looking for some help to calculate holiday hours using an "H", or meeting hours using an "M", in a time cell instead of an hour to state that it is a holiday day, and distinguishing these hours from standard working hours to create two different totals.

I currently have this:

=SUM(G27-F27)*24-IF((G27-F27)*24>6,0.5,0)

Stating - Clock Out time minus Clock In time, multiply by 24, then, if the value is greater than six hours, remove a half hour break

I want to add:

-If G27 and G26 have an 'H' (or an "M") value then take the value of a related cell in sheet123, cell123, (or sheet321, cell321 for M/Meeting) for their daily holiday hours, if these cells have a time then use above formula to work out working hours.

Also, is there a way to distinguish between holiday/meeting hours and working hours, so that I am able to automatically calculate these two totaling cells:

Trading Hours (working hours)
[SUM(H42,L42,P42,T42,X42,AB42,AF42) - easy SUM function for all total hours, but if a member of staff is on holiday I need this function to ignore their hours and add them below instead]

Non-Trading Hours (holiday / meeting hours)
[Reasonably the same formula, but in reverse - only counting H (holiday) or M (meeting) hours, and discounting all working hours]

I presume that one way of doing this is to have an H in the cell on sheet123. So, if someone is on holiday, their daily hours will be 6.00 H for example. Then use a formula to calculate total hours, which includes an H if already apparent in a cell.

However, I'm not sure how to do this, and after that, I'm even less sure of how to distinguish and calculate separately trading and holiday hours.

I know this is a lot, but if anyone can help, I would be much obliged.

Thanks in advance!

S.

----

Other forums this question is on:

http://www.mrexcel.com/forum/excel-q...cheduling.html

http://www.ozgrid.com/forum/showthread.php?t=176960