• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tricky Nanny Share Timesheet for 3 Children: Parsing Overlapping Times

M

mehenry

New Member
Hello,

I am a busy working/grad student mom of 2, and I would greatly appreciate help with a timesheet for our nanny share.


I created an Excel spreadsheet to keep track of hours for our nanny share for 2 children a few months ago. It worked perfectly, enabling us to keep track of times and total expenses for each family after we just filled out start and end times for each child.


However, now my older child is occasionally joining the nanny share, and my awesome formulas don't work anymore. The formulas just compared the total hours for each child for each day, divided up when they were together or alone, and calculated the rates for each...but I'm not talented enough to work this out for 3 start and end times. Working it out by hand is a pain...I'm hoping someone can come to the rescue!


I have the spreadsheet all set up: https://dl.dropbox.com/u/11284836/NannyShare_Hours_2013.xlsx


I can't figure out 2 things, highlighted in yellow on the spreadsheet. If I can get assistance to figure those columns out, the rest is already set up to calculate how much we owe by day and week, for each family.


Issue 1. How do I parse the overlapping times into the correct categories based on the start and end times that we fill in? For example, I want:

L4 to reflect how much time there was when just 1 child was present (A or B)

O4 to reflect how much time there was with two toddlers present (A and B)

R4 to reflect how much time with 3 kids present (A, B & C)

V4 to reflect how much time with my two kids present (B & C)


Issue 2. How do I fix M4 and N4 so only one of those cells is filled in?


In the spreadsheet, I have filled the second worksheet in by hand, just to show what I would like it to look like if I had formulas in columns L, O, R, and V. The M and N columns are still wrong, though.


Thank you so much in advance. It would be so helpful to all parties to be able to automate this as we had been doing before. I have searched this forum and the Internet, but I can't seem to get the formulas to work.


Thanks,

Megan


PS If it helps, there are 4 types of set-ups for our nanny share based on Child A (neighbors' toddler), Child B (our toddler), and Child C (our older child):

1 Toddler Alone ($12/hr each for A or B)

2 Toddlers Together ($9/hr each for A and B)

2 Toddlers + 1 Kid ($8/hr for A, $7/hr each for B & C)

1 Kid + 1 Toddler ($7/hr each for B & C)

These are already set up to calculate correctly if I can get help to parse the hours correctly...but I am open to a completely different set-up that works better, of course.
 
Well, that is one tricky problem. I am not sure if my formulas are correct as the results are not matching with your manual calculations. But I think I got the approach ok (may require few tweaks).


See the file.


http://img.chandoo.org/playground/NannyShare_Hours_2013.xlsx


My approach:


1. Insert a set of 28 columns, one each for half-hour block from 8AM to 10PM (14 hours).

2. In each cell in a row, calculate how many kids are in nanny care. then use a mapping logic (part of the rate breakdown sheet) to fetch which rate to use for that half-hour.

3. Accordingly calculate billing.


I am sure there is a smarter and shorter way to do it.


Also, If you had generic rules for nanny rates the problem can be solved easily.


For example, if the nanny rate just varies based on number of kids (irrespective of whether they are related, whether they are older kids etc.) then we can build simpler formulas.


Go ahead and try it. Let us know if you can improve the solution.
 
@balettuce
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 
You must log in or register to reply here.
Back
Top