Calculating Holiday Pay // Rota Scheduling

[myactiondesign]

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.

 

[Akashwani]

Hi and welcome to MrExcel.

I'm not sure that I fully understand your requirements, so I maybe barking up the wrong tree here!!

I think that you should consider setting up a "table" with your sheet123 & sheet321 values.
Also, you may want to put your "working day" hours and break time in cells and refer to them in your formula.

The following is sample data and example results based on that sample data.........

Excel Workbook
	A	B	C
1	H	12:00
2
Sheet123

***

Excel Workbook
	A	B	C
1	M	08:00
2
Sheet321

****

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Trading Hrs	Non Trading Hrs		Clock In	Clock Out	Total Time		Criteria	Value	Sheet	Cell		Hours	Break
2		36:00	40:00		06:00	14:00	7:30		H	12:00	Sheet123!	B1		6	0:30
3					06:00	12:00	6:00		M	08:00	Sheet321!	B1
4					H		12:00
5					M		8:00
6					08:00	16:00	7:30
7					M		8:00
8					09:00	17:00	7:30
9					H		12:00
10					14:00	22:00	7:30
11
Data1

In the above formulas you will need to change the cell references to suit your layout.
The formula in H2 needs to be copied down.

I don't suggest that this is the solution that you require, but it may help to point you in the right direction.

I hope it helps.

Good luck.

Ak

 

[myactiondesign]

Hi Akashwani, and thanks for your reply!

To be honest, I think I got more confused with your answer than my original question. I think I understand what you are saying (a little), but unsure as to how to implement it into what I have.

Here is an example of how far I've come:

https://www.dropbox.com/s/ximiuz11yldqmg3/Braehead DT Rota Model - In Progress.xls

What I've done in an attempt to accurately calculate Trading Hours is to describe full (value of 1.00) or half (value of 0.50) holiday days instead of attempting hours calculation. That way I can simply add into the formula for Trading Hours that if a cell value is less than five (five days paid holiday in one week), ignore it, if more than five, then sum.

This will work for Trading Hours, however I would still need to manually search contract hours/days for staff holidays and enter them.

Any thoughts here?

Is it more clear?

Thanks a million for trying to help

S.

-----

Other forums this post is on:

http://www.excelforum.com/excel-for...ished-from-working-hours-rota-scheduling.html

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