+ Reply to Thread
Results 1 to 8 of 8

formula to output based on date range

  1. #1
    Registered User
    Join Date
    06-23-2017
    Location
    Fort Collins, CO
    MS-Off Ver
    Office 2010
    Posts
    29

    formula to output based on date range

    I tripping over proper syntax for this:

    Where Cell A1 = DATE

    I want Cell B1 to output based on the following:
    If A1 < 01/01/1974, then X
    If A1 >= 01/01/1974 and <= 12/31/1976, then Y
    If A1 > 12/31/1976, then Z

    Eaxmple:
    A1 = 05/18/1975, B1 = Y

    I know there are some if's, ands, commas, and parenthesis involved, but I can't seem to get a structure that excel likes.

    Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: formula to output based on date range

    Probably an easier way, but try this...
    =If A1 < "01/01/1974", "X",If(and( A1 >= "01/01/1974", A1 <= "12/31/1976"), "Y","Z"))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-23-2017
    Location
    Fort Collins, CO
    MS-Off Ver
    Office 2010
    Posts
    29

    Re: formula to output based on date range

    That's the same logic I applied, but there is flaw in syntax. Excel says the formula contains an error.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: formula to output based on date range

    Sorry, left out the 1st (
    this worked for me when I entered it
    =IF( A1 < "01/01/1974", "X",IF(AND( A1 >= "01/01/1974", A1 <= "12/31/1976"), "Y","Z"))

  5. #5
    Registered User
    Join Date
    06-23-2017
    Location
    Fort Collins, CO
    MS-Off Ver
    Office 2010
    Posts
    29

    Re: formula to output based on date range

    There its is. I knew I had a problem with () not being in the right places but couldn't wrap my head around to correct pairings. Thank you!

  6. #6
    Registered User
    Join Date
    06-23-2017
    Location
    Fort Collins, CO
    MS-Off Ver
    Office 2010
    Posts
    29

    Re: formula to output based on date range

    I thought this was solved because Excel took it as usable formula, but I was wrong. The output for your formula is "X" in all cases. The correct output for my sample of 5/15/1975 should have been "Y".

  7. #7
    Registered User
    Join Date
    06-23-2017
    Location
    Fort Collins, CO
    MS-Off Ver
    Office 2010
    Posts
    29

    Re: formula to output based on date range

    The solution appears to be that all the "DATE" functions you suggested need to be replace with the property formatted function DATE(YYYY,MM,DD) resulting the following solution that works:

    =IF( A1 < DATE(1974,1,1), "X",IF(AND( A1 >= DATE(1974,1,1), A1 <= DATE(1976,12,31)), "Y","Z"))

    Marking Thread as Solved now.

  8. #8
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: formula to output based on date range

    Hi,

    I know this has been marked "SOLVED", but I just wanted to point out you don't need to Test the part in RED:

    =IF( A1 < DATE(1974,1,1), "X",IF(AND( A1 >= DATE(1974,1,1), A1 <= DATE(1976,12,31)), "Y","Z"))

    So it can just be:

    =IF( A1 < DATE(1974,1,1), "X",IF( A1 <= DATE(1976,12,31), "Y","Z"))
    Last edited by jtakw; 06-06-2018 at 04:40 AM. Reason: removed extra bracket

+ 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. STUCK** SUMIF INDEX MATCH - formula output sum of date range or month sum
    By Antprod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2015, 08:13 AM
  2. Replies: 1
    Last Post: 11-24-2015, 03:00 AM
  3. [SOLVED] Date Range Output.
    By mtilbury in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-27-2014, 07:35 PM
  4. [SOLVED] Formula to Lookup value based on date within a date range
    By MHamid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2013, 02:24 PM
  5. Output date from changing cell range
    By kolonel in forum Excel General
    Replies: 6
    Last Post: 08-08-2012, 11:43 PM
  6. Excel 2007 : Formula To Output What Range A Date Is In
    By The_Snook in forum Excel General
    Replies: 2
    Last Post: 01-07-2012, 06:27 PM
  7. Output Customer Name for a specific date range
    By mdobmeier in forum Excel General
    Replies: 5
    Last Post: 04-29-2011, 04:36 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