Solved Totaling Time from a drop down in excel

Microsoft Excel 2010 - complete product...
January 4, 2014 at 20:38:07
Specs: Windows XP, i
I have created an excel spreadsheet for my parents to sign in and out when dropping off and picking up their children. It's set up as follows:
Column A= child's name
Column B= in/out
Column C-H=Mon-Sat with drop down list of times of the day.

Under each day I would like to have a total of hours for that day.

I would like to know if there is a formula that will compute this?

Please Help!!


message edited by Awilson05

See More: Totaling Time from a drop down in excel

Report •

January 5, 2014 at 13:58:53
✔ Best Answer
The way you have your schedule set up might cause some problems.

I don't know who is going to be doing the data entry, but you might want to revise your form so it makes for easy calculations.

I would suggest something like:

          A          B           C          D
1) MONDAY                    Week Of:   01/06/2014
2) Child's Name    Time In   Time Out   Total Time
3)                                        =C3-B3
4)                                        =C4-B4
12) Child's Name    Time In   Time Out   Total Time
21) Child's Name    Time In   Time Out   Total Time

This way, you need only do a simple subtraction to get your total times: =C3-B3
Also, Each week is Dated so you know what day your using.

You can then get a weekly total using a =SUMIF()function
similar to: =SUMIF(A3:D33,"Mike",D3:D33)
Where the Range, (A3:D33) is your Week,
Your Criteria, "Mike" is the Child's Name,
and the Sum_Range, (D3:D33) is your totals column.

You can use a single tab for each month, and then do a monthly total.

Having a separate Time In and Time out, also can give you a visual cue so there should be no blank times at the end of the day. With a single Time In/Out cell you may miss a time.

There are a great number of Scheduling templates available.
Try your version of Excel.
Select File / New and you should be offered several template choices. Check'em out.

Also, MS offers a varied assortment here:

Find what suits you best and then simply modify it.


Report •

January 5, 2014 at 16:09:52
Thank you for your solutions. It was exactly what I needed. I am attempting a trial run at this for my parents to be able to sign in and out. I hope it works out! Fingers Crossed!


Report •

January 5, 2014 at 16:32:58
If your going to have parents using the spread sheet,
I would advise using a drop-down with both the first and last name of the child.

Dad may call him Joe, but Mom insists he be called Joseph......

You will find it causes less errors in data entry, if you control what the parent can choose, which in turn, simplifies your formulas when you do your totaling.


Report •

Related Solutions

Ask Question