+ Reply to Thread
Results 1 to 8 of 8

Complex IF cases

  1. 03-28-2014, 01:42 AM #1
    Chippi
    Chippi is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    27

    Complex IF cases

    Hello all, Datesmatch.xlsxDatesmatch.xlsxI need some help with these complex conditions for my data. .

    I have an input column which has 2/3/4 digits with a letter at the end in few cases( I don’t need them..need only the numbers). I have another column called the “dates” which I should use to compare with my input numbers.

    My input tells me which year and month
    eg: CASE 1:
    Input: 0901 (means 2009)
    Dates:1/12/2009
    As my 09 matches the year in the “dates” Column, I go ahead with the condition to check if the date is before 15th or after 15th of the month. If it is before 15th--then I will have to make sure my output is--> 1/15/2009

    CASE 2:
    Input: 0203
    Dates: 03/27/2002
    My year 02 matches 2002 in the “dates” column. The condition to check will be is it before or after 15th. If after 15th, my output should be the last date of that month --->03/31/2002

    CASE 3:
    Input:03J
    Dates: 12/12/2003
    The year 03 does matches my year in “dates”.But there is no month given. I should make my output show the mid of the year 2003 Output--> 7/1/2003

    CASE 4:
    Input: 06
    Dates:3/2/2009
    The year 06 does not match my year in “dates”. Hence for such cases I should make my output show the mid of the year 2006. Output-->7/1/2006


    I have attached a sample excel workbook.

    Thanks,
    Chippi
    Reply With QuoteRegister To Reply
  2. 03-28-2014, 02:55 AM #2
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Complex IF cases

    Hi
    Maybe this? Tried to consider cases you wrote here in post.
    c2 = =CHOOSE(IFERROR(AND(ISNUMBER(--LEFT(A2,4)),LEN(--LEFT(A2,4))>=3,1*LEFT(A2,2)=RIGHT(YEAR(B2),2)*1,--MID(A2,3,2)=MONTH(B2),DAY(B2)<15),0)*1+
    IFERROR(AND(ISNUMBER(--LEFT(A2,4)),LEN(--LEFT(A2,4))>=3,1*LEFT(A2,2)=RIGHT(YEAR(B2),2)*1,--MID(A2,3,2)=MONTH(B2),DAY(B2)>=15),0)*2+
    IFERROR(AND(1*LEFT(A2,2)=RIGHT(YEAR(B2),2)*1,OR(NOT(ISNUMBER(--MID(A2,3,2))),IFERROR(1*MID(A2,3,2),0)<>MONTH(B2)*1)),0)*3+
    IFERROR(AND(1*LEFT(A2,2)<>RIGHT(YEAR(B2))*1,OR(IFERROR(--MID(A2,3,2),0)<>MONTH(B2),NOT(ISNUMBER(--LEFT(A2,4))))),0)*4,
    DATE(YEAR(B2),MONTH(B2),15),EOMONTH(B2,0),DATE(YEAR(B2),7,1),DATE(LEFT(A2,2)+2000,7,1))
    Attached Files Attached Files
    Appreciate the help? CLICK *
    Reply With QuoteRegister To Reply
  3. 03-28-2014, 05:10 AM #3
    Chippi
    Chippi is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Complex IF cases

    Thank you so much for your help. It works for most of them but for

    values like

    10 10/10/2010--->#VALUE!
    V1103 5/26/2011---->#VALUE!
    1204 5/3/2012----->#VALUE!
    0103 3/10/2013---->#VALUE!



    Can you look into these please.

    Thanks,
    Chippi
    Reply With QuoteRegister To Reply
  4. 03-28-2014, 07:50 AM #4
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Complex IF cases

    Used helper column. see attached file.
    =CHOOSE(AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)=MONTH(C2)*1,DAY(C2)<15)*1+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)=MONTH(C2)*1,DAY(C2)>=15)*2+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)<>MONTH(C2)*1)*3+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)<>RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)<>MONTH(C2)*1)*4,
    DATE(YEAR(C2),MONTH(C2),15),EOMONTH(C2,0),DATE(YEAR(C2),6,1),DATE(LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)+2000,7,1))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-28-2014, 12:59 PM #5
    Chippi
    Chippi is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Complex IF cases

    Thanks AZ-XL I appreciate it
    Reply With QuoteRegister To Reply
  6. 03-29-2014, 03:57 AM #6
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Complex IF cases

    You are welcome. Thanx for feedback
    Reply With QuoteRegister To Reply
  7. 04-03-2014, 09:11 AM #7
    Chippi
    Chippi is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Complex IF cases

    Hi.. sorry to reopen this case. Im having issues with the formula. Can you please look into again? I need to fix it soon.



    Helper Dates O/P
    0101 01/21/2011 #VALUE!
    1109 2/21/2014 #VALUE!


    Pls help me out.

    Thanks,
    Chippi
    Reply With QuoteRegister To Reply
  8. 04-03-2014, 01:18 PM #8
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Complex IF cases

    Quote Originally Posted by Chippi View Post
    0101 01/21/2011 #VALUE!
    1109 2/21/2014 #VALUE!
    Chippi
    Tested examples above and second one gives correct answer in previous function. Its result is 1-Jul-11
    But first one give an error as you mentioned. Try this formula. Paste to e2 and drag down. Result of function is: 1-Jul-01

    =CHOOSE(AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)=MONTH(C2)*1,DAY(C2)<15)*1+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)=MONTH(C2)*1,DAY(C2)>=15)*2+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)=RIGHT(YEAR(C2),2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)<>MONTH(C2)*1)*3+
    AND(1*LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)<>RIGHT(YEAR(C2),2)*1,OR(1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)=MONTH(C2)*1,1*RIGHT(IF(LEN(B2)=4,B2,B2&"00"),2)<>MONTH(C2)*1))*4,
    DATE(YEAR(C2),MONTH(C2),15),EOMONTH(C2,0),DATE(YEAR(C2),6,1),DATE(LEFT(IF(LEN(B2)=4,B2,B2&"00"),2)+2000,7,1))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Add cases opened and closed for each month and specifics about the cases.
    By ChrisLaRoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2014, 08:19 PM
  2. cases in forms
    By FRIEL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2008, 04:49 AM
  3. Cases within Cases
    By mtm4300 via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-18-2006, 09:45 PM
  4. Matching 2 cases at once.
    By mae1778 in forum Excel General
    Replies: 2
    Last Post: 08-26-2005, 05:05 PM
  5. Three Cases
    By teresa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2005, 02:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1