Calculate hours worked from dropdown list for start and stop times

I'm creating a combination schedule-timesheet for my employees. The doc will show employees the hours they are scheduled to work each day. It will also allow them to indicate their actual start and stop times and calculate actual hours worked.

- Employee names are down left hand column.
- Time of day in half hour increments is shown across top row.
- This array is repeated down the page, one block of employee names and time of day for each day of the week. E.g.:

MONDAY
7am - 7:30am - 8am - 8:30am - 9am - 9:30am - 10am... Actual Start Actual Finish Total Hours
Jane
Joe
Jack

Tuesday
7am - 7:30am - 8am - 8:30am - 9am - 9:30am - 10am... Actual Start Actual Finish Total Hours
Jane
Joe
Jack

- Hours that each employee works on a given day are indicated by colouring in the cells that cover the range of times they are supposed to work.
- At the end of the row with times are two cells with drop down menus for Actual Start and Finish times and a cell with total Total Hours worked.
- The drop down menus are populated with times of the day in 15 minute increments: 7am, 7:15am, 7:30am, 7:45am, 8am, 8:15am, 8:30am, 8:45am, 9am,...1pm,1:15pm etc
- I need a way to calculate actual hours worked from the start and stop times that the employee chooses from the drop down menu, presumably with a formula that can use values from the selected start-finish times from the drop downs.
- The times that populate the drop down menus are from a list that I created so they aren't formatted as actual times. This is because there didn't appear to be an option to populate the list with actual formatted times. The problem is somewhat tricky (for me) because I don't know how to calculate total hours from a start time of say 7:15am and finish time of 5:45pm, or even whether a formula can work with a value selected from a drop down menu.
- I'm guessing I need to correlate each item from my list to a time on the 24 hour clock, 7:15am=07:15 and 5:45pm=17:45. I have no idea how to do this.

Maybe I'm going about this is an unnecessarily complicated way. If anyone else has or knows how to create a spreadsheet that will accomplish my goals in a less complicated way I'm all ears!

Thanks.

Hi van,

You want to format your cells as a time. You can use military time or AM:PM. In your list, make sure that you seperate AM/PM from the number, otherwise it will default to text. Then END - START will show hours and minutes. Multiply this number by 24 and format as a Number. ie 12:30 (12 hours 30 minutes) becomes 12.5

Thanks very much for the reply mmogharreban.

I tried your suggestion and it works like a charm.

One large wrinkle though...I've done a little more investigation and, since I'd ultimately like to use this spreadsheet in Google Docs, and since Google Docs spreadsheets (unbelievably) don't allow items in data validation to be formatted for time (Date is the only option that's close), this solution won't work.

It's pretty amazing to me that all the big players, like Google, want to push everyone into using their web-based apps when those apps are so limited in functionality!

Any other ideas? Is there a way of creating a table behind the scenes that will tie each of my Start/Stop Times to an actual time? I'm concerned now that this limitation in Google Docs will make this task even more difficult and possibly unfeasable.

Thanks also Km.

That's a helpful way of looking at it. The only thing I'm wondering about is how to make that work with a drop down menu. I'd like my employees to see actual times listed in the menu, e.g. 7:15 am so I'm not 100% clear on how your solution would fit in with that requirement. It seems like it would result in the menu looking like: +7.25/24, +7.5/24, etc and while my emplyees are pretty bright I'd like to make it as user friendly as possible. Also still not clear on whether the formula for total hours will pull whichever value is selected from the drop down. Is that going to work for sure?

Hi Van,

Are you using data validation to validate time or create your dropdown list? If you format your list of available times and the entry cell as a time value (Format>Number>More Formats for Google Docs) and use the validation setting for List, you should have no problems.

Howdy mmogharreban.

I'm using data validation to create the drop down menus.

I can see now that time only is a formatting option for cells but still not sure on how to similarly format the validated data as time only. I tried formatting the cell where I'm creating the drop down to time only, then created the drop down using validation, but there is still no option for a time format there. Am i missing a step?

Sorry Van, I am not quite sure what problems you are having. For arguments sake, you could have the entire sheet formatted as a time, so long as the cell you are using to calculate your total is formatted as a number. You should not need to set a format for the times displayed in the dropdown, they will correspond to what you have already set in the cell.