+ Reply to Thread
Results 1 to 5 of 5

#Value error

  1. #1
    Registered User
    Join Date
    10-19-2009
    Location
    tyne & wear
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    37

    #Value error

    Hi All
    Office excel 2003
    got a times worked sheet but if text entered in a cell #Value error appears & destroys formular can I add to formular to ignore text in cells & only count time please find attached
    sheet starts monday to sunday start & finish times with 1/2 hour deducted for lunch but need to fill in when someone on sick course or holday these are in a drop down list

    Many thanks
    Attached Files Attached Files
    Last edited by hugrl; 12-06-2010 at 07:25 AM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: #Value error

    before we go there what is this bit supposed to be doing?
    IF(D5>E5+F5>G5+H5>I5+J5>K5+L5>M5+N5>O5+P5>Q5,0)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-19-2009
    Location
    tyne & wear
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    37

    Re: #Value error

    [QUOTE=martindwilson;2429541]before we go there what is this bit supposed to be doing?
    IF(D5>E5+F5>G5+H5>I5+J5>K5+L5>M5+N5>O5+P5>Q5,0)[/QUO
    it part of formular I was given

  4. #4
    Registered User
    Join Date
    11-18-2010
    Location
    Johannesburg South Africa
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: #Value error

    Hi,
    Use ISTEXT or ISNUMBER function.
    Look at attached sheet and notice I added a collumb for hours worked for Monday.You can do that for every day and ad all the hours for the week in the last col.
    You can do it all in one formula in the last col.,but as you can see, it will be a very long and complicated formula.
    Be sure to format the "Hours" collumb as number, else your result could be in "Time of day" instead of hours.

    Regards
    Dan
    Attached Files Attached Files
    Last edited by DanExel; 12-06-2010 at 05:01 AM.

  5. #5
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: #Value error

    I think the version you have is incorrect (regardless of text) assuming you wish to deduct 30 minutes per day.

    If the shifts never cross midnight then the below should suffice:

    Please Login or Register  to view this content.
    If shifts do cross midnight you can use a SUMPRODUCT approach with MOD (though an Array may prove to be preferable given Text etc...)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1