Excel: calculating childminder's pay

[jamaisjedors]

I calculate our childminder's pay, with a basic monthly rate plus any extra hours she does, plus meals and plus a maintenance charge based on the number of actual hours done.

I have a reasonable spreadsheet set up for this, but find 2 things very tedious at the end of every month:

1. entering in for every day of the month the exact number of hours per child (can I put the start and finish times and have it calculate how many there are?)

1. calculating the overtime.

Each child "should" do 13hrs per week. So I have a cell with overtime = time done -13

Except this doesn't work, because some weeks they only do 4 hours (1st week of October, there was only a Friday in it).

So I have to look at every day and see what they should have done and then add up the extra manually.

Not sure if anyone can help with this, I am not mathematically minded!

You can work out the hours on the basis of the start and end time but you have to decide what your minimum time unit is (an hour, half an hour, a quarter of an hour, etc) and then how you are going to round up or down the hours after you put the time in. If you can do that, I can tell you what the formula is.

If you're doing it in Excel the easiest thing might be to have a basic sheet with 31 rows in it (below some stuff at the top). You put the month into one cell, the rows work out what day they each are, and you fill in the hours for the appropriate days, then hey presto. But you need to decide that basic stuff about what exactly constitutes an hour first.

Thanks.

At the moment I have it in rows for weeks, but it sounds more logical to put the days in a row.

I round up all hours to a whole one - ie 7h45 to 8.30 is an hour, as is 4.30 to 5.

Sorry I took a while to get back to you on this.

What you need to do is have 2 cells to say what month and year you're in, then 31 rows with the numbers from 1-31 in a column. If the year is in cell A3 and the month in cell B3, and the numbers of the days in column A, the formula to go in column B next to the numbers 1-31 is then "=DATE($A$3,$B$3,A5)" and if you put that in to the cell for the first day and fill down, the rest of the cells in column B will work out what day they are on the basis of the month and year you put into A3 and B3. The month and year should be entered in numbers but you can format the cells to make them, and the dates, show up in words.

You can then have another two columns for start and end time, formatted as time. "Formatted as Time" is important because then to the right of that you can do this: "=ROUNDUP(24*(D5-C5),0)" (if your start time is in C and end time is in D) to give you the time worked rounded up to whole hours, which you can then, in the next column, multiply by your hourly rate. (or you could do it in the same calculation if you want) Bear in mind that 3 hours and 5 minutes would turn into 4 hours in this scheme, but you did say you wanted to round up. If the start time or end time is usually the same you can fill them in with a "fill down" to start with and then change them.

Then put your monthly rate, maintenance charge etc into cells at the top somewhere so you can refer to them in your final calculation and hey presto, every month all you need to fill in are the month and the hours actually worked, and the answer will come out.

Hope that helps- I know it's not actually a piece of cake but given the number of variables it never would be.

Thanks so much. I am trying to get this all straight in my head, will test it out later and get back to you if I have a problem.

You are great!