Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula Problem #value!

    I have the following formula in cell J1 on sheet TB Consolidation

    =IF(DATE("20"&MID(F3,4,2),MID(F3,7,2),28)>DATE("20 "&MID(I3,4,2),MID(I3,7,2),28),"Y",IF(DATE("20"&MID (F3,4,2),MID(F3,7,2),28)=DATE("20"&MID(I3,4,2),MID (I3,7,2),28),"CURR","N/A"))

    It gives the result #value!. I think that I need to include a text function within the above formula, but I not sure how to incorporate this within another formula in order for the error to be rectified

    I have also attached some text data referring to the respective cell numbers

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Cell F3 contains "40542 YTD", so using MID(F3,7,2) to give the Month value of your first date, results in "YT".
    Similarly, I3 contains "40544 YTD", so you're getting "YT" as the Month part for the second date in your IF statement.
    Last edited by Gfamily; 2011-03-15 at 12:57.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    It's worth noting that if you click on the 'fx' or '=' icon just to the left of the formula edit bar it'll give you a breakdown of what the formula does. Then, if you click on each of the functions within the formula it'll tell you what's happening with each
    .Function.jpg
    Last edited by Gfamily; 2011-03-16 at 05:28.

Posting Permissions

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