daddylonglegs Wrote:
I cant see why the decimals won't work if the hours does, it works for
me:)
If you want to ignore text use
=SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24))*24
confirmed with CTRL+SHIFT+ENTER
format as general or number
note: that this won't cope with "night shifts", i.e. days that start
before but end after midnight, e.g. 23:00 - 07:00 - for that amend to
=SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24+(LEFT(A1:G1,5)RIGHT(A1:G1,5))))*24
Is it possible to adjust your formula so that if there is a shift less
than 8 hours (e.g 09:00-13:00 or 10:00-14:00) that it does not deduct
an hour for lunch.
Cheers!
BankC
--
BankC
------------------------------------------------------------------------
BankC's Profile:
http://www.excelforum.com/member.php...o&userid=26337
View this thread:
http://www.excelforum.com/showthread...hreadid=501797