Single cell time entry formula?

**havamal79** · 10-04-2012, 06:00 PM

Hi, my boss wants me to type up a spreadsheet using for hours worked in a day (minus lunch breaks of 30-60 minutes), but wants it done in a single cell with the total amount of hours worked displayed in the cell below it. Is there an equation/formula for this? Thanks! I've included a jpeg to show what I mean.

untitled.JPG

**Pete_UK** · 10-04-2012, 06:23 PM

I wish people would not post jpegs - all you can do with them is look at them - why not post a workbook?

Anyway, it looks like B9 is the first cell where you have some "time ranges" above, so you can try out this formula in B9:

=IF(or(B8="In",B8="off",B8=""),"",(--(right(B8,len(B8)-search("-",B8))&":00")+0.5-(left(B8,search("-",B8)-1)&":00"))*24)

That should give you 9.25 hours, but it is not clear how much time should be subtracted for lunch.

Hope this helps.

Pete

**havamal79** · 10-04-2012, 07:01 PM

Thank you Pete, my experience with Excel is still fairly limited though so a jpeg was the quickest way for me to visualize what I had going on. The formula worked too, just have to plug in the rest of the cells, thanks again, cheers!