Total hours between two times in same cell

[DanAnderton]

Hi there,

I'm working on a staff register and would like to calculate the total hours worked, my problem is I want the start and end of the shift to be in the same cell, I'd also like to deduct break time (as we don't get paid for breaks). Here's an example of what I'm looking for:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Total Hours[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]Jon Doe[/TD]
[TD]15[/TD]
[TD]7am - 4pm
1.5 hour break[/TD]
[TD]7am - 4pm
1.5 hour break[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas on how I can do this? I've seen a template out there that does exactly this, but I'd like to build on the concept to calculate total hours worked Mon - Sat differently to Sunday.

Any help would be greatly appreciated.

 

[mole999]

you can fake the look by changing the cells grid lines to white and that will make the whole process much more stable and less prone to issues

 

[DanAnderton]

I tried that, but I'm trying to make the whole thing simple to input and read. Think I've found a solution though by calculating contracted and overtime hours on a different sheet as the contracted doesn't change.

One problem I'm running into now though is with an IF statement. I'd like to use a function within the statement, example:

=IF(F8="HOL",HoursF7),0)

But I keep getting the #value error. Any idea what I'm doing wrong?

 

[DanAnderton]

Don't think I explained that very well.

F8 refers to a cell below the shift where I put in HOL if the person is on holiday. F7 is the cell containing the shift

 

[DanAnderton]

Scrap that sorry, I've just figured it out that i didnt need the :