Rota - calculate the hours every one has done each week

**mrjaffa** · 11-19-2007, 09:27 AM

Hi everyone.

I'm not very useful with Excel but I've just finished doing my staff rota and was wondering if there is an easy way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever, lol.

I've included a zip file of the excel file and I would REALLY appreciate it if anyone could take a look at it and advise me.

Thanks very much.

**oldchippy** · 11-19-2007, 09:46 AM

You may be better off redesigning your sheet so that it is easier to do the calculations, take a look at this link on TIME calculations

http://www.cpearson.com/excel/overtime.htm

**mrjaffa** · 11-19-2007, 10:29 AM

Thanks for your reply.

I figured it would be difficult to do with my design, so if there is no way I'll certainly take your advice. I'll just add an extra column next to each day for hours worked that day and then calculate that way.

Now to try and remember how to do a simple sum like that.

:-)

**oldchippy** · 11-19-2007, 11:05 AM

May be something like this? I've completed the first column and top row and put the formulas in place.

**mrjaffa** · 11-19-2007, 02:31 PM

Hey again. Thanks for your help.

I've taken your advice and tried to re-design it, but I'm struggling.

Can you take a look at my latest effort and tell me why the sum function I've used to calculate the hours worked for one week doesn't seem to be working.

Thanks!

**oldchippy** · 11-19-2007, 06:41 PM

Can you attach your example

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**mrjaffa** · 11-19-2007, 07:45 PM

Blimey, thought I had, sorry about that.

Let me try again.

**oldchippy** · 11-20-2007, 05:38 AM

Hi,

Wasn't the one I posted to you (post 4) any good?

With yours, when you work out the times, instead of a decimal format use a time format, so for instance 8:30 - 16:15 would be 7:45, then just sum the daily cells, but format the total cell to a Custom format [hh]:mm

**mrjaffa** · 11-20-2007, 06:25 AM

Hi again.

The reason I didn't use yours was simply because I would have had to enter all the data again and I'm suppose to stick to that template as requested by my RM.

Now then, I did as you said, formatted the cells correctly and changed the hours but it's now coming up with the total of 0. Very confused.

You think you could take one last look?

Thanks buddy.

**oldchippy** · 11-20-2007, 06:47 AM

Try this, have formatted the total cell to [hh]:mm and included ALL days, then you can drag this down the column to make it easier.

Also check your hours calculations, someone lokks like their missing a break?

**mrjaffa** · 11-20-2007, 08:36 AM

You're a legend. Thank you.

I've attached the final version for you to marvel at.

Thanks again.

P.s. the day with no break is a Sunday and we just take 30 mins paid.

**oldchippy** · 11-20-2007, 08:44 AM

Glad to help - well done, it a pity though you can't get the RM to change the template, only there's a lot of duplication in there and also you have to work out the hours each person is there, where as Excel can work all that out for you.

If you need any more help let me know.

**mrjaffa** · 11-20-2007, 09:39 AM

In the future though, I will create a template (the one I have now just without all the hours), then stick the new hours in and it should be so much simpler for me.

**oldchippy** · 11-20-2007, 09:40 AM

OK, best of luck

**mrjaffa** · 11-25-2007, 09:00 AM

Hey again.

I was hoping for some more help off you o'wise one.

I got thinking and I was wondering if there is any king of programming I can use that would help me to get rid of the extra column I have at the end of every day for "Hrs".

Can I not use some programming so I could say for example, "8:30 - 17:45 = 8:15 hrs" and then use the formula I have without having to use this extra cell which just states the Hrs for the day.

Did that make sense at all?

:-)

**oldchippy** · 11-25-2007, 04:40 PM

Hi mrjaffa,

Yes, this can be done the way you want it, but it relies on you or the person entering the data to get the spacing and characters right each time for the formulas to work correctly. The formulas would be based on searching the text, for example for ":" or a space, or a dash, if these are missed out then the formulas will fail. So before we start is it possible to alter the template slightly?

Rather than putting someones initials in the cell and the times on/off for each day, is it possible to have a column at the left hand side for their initials so that you only need to enter those once in rows 4-9 for week 1, weeks 2-6 can then be linked back, so less input for you...less mistakes?

Also can we split the on/off times into separate cells?

We could also make the dates automatically appear by entering just one date, same with the days of the week.

Let me know what you think?

**mrjaffa** · 11-27-2007, 01:57 PM

Thanks again for your reply.

I think I should be able to change the template, yeah. I did think about taking the initials out like you 've suggested, so that should be cool.

I'm not sure where to start though, lol.

**mrjaffa** · 11-27-2007, 02:32 PM

Hey again.

I've included a template designed on the one you did originally for me.

:-)

**oldchippy** · 11-28-2007, 07:30 AM

Hi mrjaffa,

Try this out, seems to work OK. I've hidden columns to calculate the times. Let me know if you need any more help.

**mrjaffa** · 11-28-2007, 04:27 PM

That seems to be fantastic!

Works a treat.

But this column you've hidden, how did you do that? And how do I view it again? Just so I can see what you've done and have learnt something.

Thanks again....

P.s. You're a moderator here yeah? Does the site take donations?

**oldchippy** · 11-28-2007, 06:58 PM

OK, glad you like it.

You will see the column headings are missing D,G,J,M,P,S,V, these are hidden, to unhide them, either select C,E then right click, Unhide, or if you want to unhide all at the same time then select C - W, right click, Unhide.

To hide them again click each column, while holding the Ctrl key, then right click again, but Hide.

You will see all the formula's used, for example

=IF(B4="",0,IF(OR(ISTEXT(B4),ISTEXT(C4)),0,C4-B4))

This says if B4 is blank put a zero, if B4 or C4 contain text put a zero, otherwise take Out-time from In-time

Then each of these cells are added in the SUM in W4.

As for

Does the site take donations?

Hearts, lungs, kidneys etc.

**mrjaffa** · 11-28-2007, 08:13 PM

Maybe a kidney..............

Thanks again. I've done a tiny bit of programming before so I can kinda make sense of that.

One thing I don't get is how you seem to have taken into consideration the hour lunch when calculating the hours.....

Also, I've tried deleting all the hours so I just have a template for when I come to doing next months but when I delete the hours it also seems to delete the formula. Any way round this?

**oldchippy** · 11-29-2007, 05:03 AM

One thing I don't get is how you seem to have taken into consideration the hour lunch when calculating the hours

If you look at one of the hidden columns - G, I have just put 1:00 in cell G3 then linked the formula to that to deduct the one hour for lunch. You said this didn't apply to Sunday, so it doesn't appear in column D.

Also, I've tried deleting all the hours so I just have a template for when I come to doing next months but when I delete the hours it also seems to delete the formula.

I thought you might ask about this one, so what I did was to select all the the formulas in the hidden columns and also the formulas in column W and put Protection on them (select cell > Format > Protection > Locked).
Now for this to work, once you have hidden your formula columns, go to Tools > Protection > (no need for a password, if you don't want one), then click OK.
You should now be able to select all columns with the times in and delete them. If you go over one of the protected columns, you will get a message to say you can't delete those.

BTW, I notice that the one hour lunch break doesn't apply to Christmas Day, as it's a one off, the easiest way is to put 1:00 in cells Z32 and Z37 then adjust the formula in W32 and W37 to add the 1:00 to the sum total, when you update your calendar for the next month (DON'T FORGET) just delete Z32 and Z37.