# Calculating Holiday Pay // Rota Scheduling

#### myactiondesign

##### New Member
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

-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:

[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.

S.

Last edited:

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Akashwani

##### Well-known Member
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
ABC
1H12:00
2
Sheet123

***

Excel Workbook
ABC
1M08:00
2
Sheet321

****

Excel Workbook
BCDEFGHIJKLMNOPQ
236:0040:0006:0014:007:30H12:00Sheet123!B160:30
306:0012:006:00M08:00Sheet321!B1
4H12:00
5M8:00
608:0016:007:30
7M8:00
809:0017:007:30
9H12:00
1014:0022:007: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

##### New Member

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

Last edited:

Replies
20
Views
671
Replies
5
Views
244
Replies
1
Views
192
Replies
1
Views
164
Replies
34
Views
527

1,196,008
Messages
6,012,836
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back