1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel formula help

Discussion in 'Business Applications' started by cjclark, Sep 30, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. cjclark

    cjclark Thread Starter

    Joined:
    Sep 30, 2008
    Messages:
    1
    need help with a formula... I want the results to be in column AA

    If V6 is blank I want the text in aa to read Booking? , if V6 has a date in it I want the text in aa to read Sailed? , if B1-V6>4 I want the text in aa to read Sailed??, if B1-V6>7 I want the text in aa to read Documents?


    I have been trying to get this to work and I am not sure if it can be done

    Thanks
    Chris
     
  2. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    hi cjclark,

    First, you can't test for a cell holding a date in the spreedsheet, you have to add a Function in a VB module and then use that in your spreedsheet:-

    Function DateTest(d As Range) As Boolean
    DateTest = IsDate(d.Value)
    End Function


    Next, I'm not sure what you wanted if B1 did not contain a Date, so I've catered for that also,
    and the result for the date difference >4 is the same as V6 = a date (i.e. "Sailed", so I've ignored that,
    then there's a catch 22: you can't test for a Date Difference until you know you have a Date; if you test for the Date first it will always come out 'Sailed'.
    I presume V6 could contain a value other than a date?
    so here's my offering in AA:-

    =IF(V6="","Booking?",IF(NOT(datetest(V6)),"NO V6 DATE",IF(NOT(datetest(B1)),"NO B1 DATE",IF((B1-V6)>7,"Documents",IF(datetest(V6),"Sailed?")))))


    Notes: is V6 a fixed reference, i.e its the same for all rows down AA, if so then use $V$6 in the above instead.
    V6="" is different to V6=" ", the former is an empty cell, the latter is blank.
     
  3. Gombot

    Gombot It's My Birthday!

    Joined:
    Mar 11, 2008
    Messages:
    82
    You can test for a date in a roundabout manner using the CELL worksheet function.

    =CELL("format",A1) will return "Dx" where x is a number from 1 to 5 if A1 is formatted using a built-in date format.

    For example, a date format of "10-Sep-64" returns "D1". Whereas a date format "10-Sep" returns "D2".
     
  4. Gombot

    Gombot It's My Birthday!

    Joined:
    Mar 11, 2008
    Messages:
    82
    So one way to accomplish your goal is a formula something like this:


    =IF(ISBLANK(V6),"Booking?",IF(CELL("format",V6)="D2",IF(B1-V6>4,IF(B1-V6>7,"Documents?","Sailed??"),"Sailed?")))


    This assumes a date in B1, A date (formatted like "10-Sep) or nothing in V6, and the formula itself in AA.

    If you are using a different date format, use =CELL("format",V6) to identify the appropriate "D-Code" to use in the Format function above.
     
  5. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    hi both,

    using the 'Cell format' does not test if a cell contains a date; it tests if a cell is Formatted to display a Date.

    So a cell so formatted but containing say 'XXX' will still return a D1 to D5 result.

    The only way to test for an actual dates is as I said via a vb function
    lol
    Hew
     
  6. Gombot

    Gombot It's My Birthday!

    Joined:
    Mar 11, 2008
    Messages:
    82
    Technically that's correct, and as always garbage-in, garbage-out.

    However, even though "xxx" will still return a "D-value" (if the user puts garbage in) the evaluated result will be "#VALUE!" (unless an error handler is added as you appropriately included in your function). So we at least get an indication of GIGO, and do not get a false "Booking", "Documents", etc.


    It's at least a work-around for people not comfortable creating new functions in VB modules.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754991

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice