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

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

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

----

Other forums this question is on:

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

2. Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Any chance of giving us some sample data to make it easier?

3. Re: 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.

4. Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Just looking through the file... Be back in a while

Thanks Andy!

6. Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Put this in H28... This eliminates the error messages by only calculating the hours if both in and out times are present.

=IF(AND(ISNUMBER(F28),ISNUMBER(G28)),(G28-F28)*24-IF((G28-F28)*24>4,0.5,0),0)

You can then copy/paste the formula down through the total hours calculation columns and it eliminates the errors.

7. Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Thanks Andy!

=IF(AND(ISNUMBER(F28),ISNUMBER(G28)),(G28-F28)*24-IF((G28-F28)*24>4,0.5,0),Sheet1!C14)

I had a bit of value error trouble with trying to put 5H in sheet1 (meaning five hours holiday).

What I've done to make things easier, is made sheet1 count whole or half day holidays.

So, if it is a full holiday, the value is 1, if half, the value is 0.5.

Now what I do is state in the totaling formula for Trading Hours that if a cell has a value of less than five, ignore it (holidays excluded).

OK, OK. So I still have to check contract holiday hours, manually enter them, but I'm getting there.

If you have any further ideas.

S.

8. Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

Absolutely mate .. It's knocking off time for me tonight but I'll get back onto it tomorrow ...

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1