+ Reply to Thread
Results 1 to 10 of 10

Count Hours but Exclude Cells containing Text.

  1. #1
    Registered User
    Join Date
    01-06-2017
    Location
    NZ
    MS-Off Ver
    2007
    Posts
    30

    Count Hours but Exclude Cells containing Text.

    Hi guys,

    I am currently using the following formula: =(C11-B11)*24-IF((C11-B11)*24>7,0.5,IF((C11-B11)*24>=5,0.5,0)) to return the calculation of hours between the two cells.

    So if cell B11 contains 8:30 and cell C11 contains 16:30 then this will return the count of hours between these two cells as 7.50.

    However if I enter any text in cell C11 or B11 then it returns an error. is there a way if I was to enter in the text as either AL, SL, DIL or PH it would still return the value 7.50. Then finally if the Cell was to contain the text RDO then it would ignore the text and return the value 0.

    Any help is appreciated with this, as I don't fully know how to formulate this.

    Cheers,
    Marc

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Count Hours but Exclude Cells containing Text.

    Create a named range containing the AL, SL etc (but not RDO) and name it "leaves" then use the following:
    Please Login or Register  to view this content.
    Or, you can do it without using a named range.

    Please Login or Register  to view this content.
    Last edited by pjwhitfield; 01-06-2017 at 04:27 AM.
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    01-06-2017
    Location
    NZ
    MS-Off Ver
    2007
    Posts
    30

    Re: Count Hours but Exclude Cells containing Text.

    Many thanks however when I type in RDO it returns 7.50 which is an incorrect value. it should still reflect 0 when RDO is typed in.

    Other than than it works perfectly.

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Count Hours but Exclude Cells containing Text.

    hmmm,

    what are you showing in the other cell? is it a number or one of the text options?

    ie if B11 = "RDO" what does C11 show?

  5. #5
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Count Hours but Exclude Cells containing Text.

    I think its the order the tests are carried out, try

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-06-2017
    Location
    NZ
    MS-Off Ver
    2007
    Posts
    30

    Re: Count Hours but Exclude Cells containing Text.

    B11 has RDO as text, C11 also has RDO as text. I have tried leaving C11 blank as well. But still D11 returns value 7.50

  7. #7
    Registered User
    Join Date
    01-06-2017
    Location
    NZ
    MS-Off Ver
    2007
    Posts
    30

    Re: Count Hours but Exclude Cells containing Text.

    Quote Originally Posted by pjwhitfield View Post
    I think its the order the tests are carried out, try

    Please Login or Register  to view this content.
    Perfect! Thanks a lot for your help!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Count Hours but Exclude Cells containing Text.

    Works for me with all conditions you specified (with corrected formula).

    And why are you testing >7 AND >=5?

    Testing >=5 meets both conditions.
    Last edited by JohnTopley; 01-06-2017 at 04:55 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Count Hours but Exclude Cells containing Text.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  10. #10
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Count Hours but Exclude Cells containing Text.

    Quote Originally Posted by JohnTopley View Post

    And why are you testing >7 AND >=5?

    Testing >=5 meets both conditions.
    Thats been bugging me however I removed the excess IF statement before answering the query and suddenly the formula didnt seem to work and I now got 12:00 returned instead of 7.5, it was really bugging me.

    Only just realised that the cell i was adding the new version in was formatted wrong! (idiot!) couldnt see the wood for the trees

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count If/Or and Exclude blank cells
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2016, 04:56 PM
  2. [SOLVED] Count according to date & exclude empty cells
    By Lukael in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-06-2015, 04:39 AM
  3. [SOLVED] Count cells but exclude 2 values
    By Karen13 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-16-2015, 01:08 PM
  4. Calculate hours between 2 dates - exclude weekend hours
    By awhitworth in forum Excel General
    Replies: 4
    Last Post: 05-26-2015, 03:32 AM
  5. [SOLVED] How To Calculate Hours Between 2 or 3 Days Exclude Non Working Hours?
    By Fazrullah Jaini in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2014, 09:54 AM
  6. [SOLVED] Count number of cells that contain text but exclude cells that contain quotation marks
    By Cantyman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2012, 03:57 PM
  7. [SOLVED] Count Nonblank cells and exclude Sub Totals
    By Seve in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-23-2012, 01:17 PM

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