# Excel template for daycare billing?October 9, 2007 8:43 AM   Subscribe

Looking for an Excel template to track children's time (billable hours) over a month.

I am looking for an Excel template/formula that will:

1. Allow entry of daily arrival and pickup time for each child (20-30 total kids), in the form "3:15 pm" or maybe "3:15" (definitely not 15:15).

2. Subtract departure time from arrival, giving a number of hours, i.e. 3.5 hours.

If there is some Excel formula to do this, please post it! I've fiddled with TIME() and TIMEVALUE() fxns with no success. This is for employees who are not programmers and actually fairly computer-phobic, so it should be simple and sturdy (and free--it's a non-profit).
posted by OlderThanTOS to Computers & Internet (6 answers total) 2 users marked this as a favorite

For the formatting of the cell, you just need to go to the "cell formatting" menu (control+1). The following formula should work:

=((HOUR(C2)*60+MINUTE(C2))-(HOUR(B2)*60+MINUTE(B2)))/60

Where C2 is where you keep "departure time" and B2 is where you keep "arrival time"

If you have an excel spreadsheet already that you just need these little features added to, you can send it my way. Email is in profile.
posted by toomuchpete at 9:03 AM on October 9, 2007 [1 favorite]

toomuchpete, does that allow for the cases where the kid is dropped off in the morning and collected in the afternoon? (ie; 9am - 1pm) Does Excel automagically do something with time values?
posted by jacalata at 9:17 AM on October 9, 2007

You'll have to tell it am/pm when the data in inputted, but the HOUR() function returns a number 0-23, so it'll handle am/pm just fine.

On the other hand, it only works on the same day, so if you span midnight it won't make any sense and will give you a negative number.
posted by toomuchpete at 9:21 AM on October 9, 2007

toomuchpete you rock! This will work fine for me, as all children are definitely picked up on the same day as they are dropped off!
posted by OlderThanTOS at 10:07 AM on October 9, 2007

And you probably already know about the keyboard shortcut CTRL+SHIFT+: that will enter the current time in the cell...
posted by Ferrari328 at 11:06 AM on October 9, 2007

Ferrari328---that's really cool. And indeed, toomuchpete's formula deals beautifully with the AM/PM issue. So the combination of people just type in the [ctrl][shift]: in the little box and the formula that automatically computes things, and you're set! Neat!
posted by leahwrenn at 6:54 PM on October 9, 2007

« Older I can't share but I can buy?   |   Painting a guitar case Newer »