+ Reply to Thread
Results 1 to 12 of 12

Looking for formula to return a date that falls between 2 dates

  1. #1
    Registered User
    Join Date
    03-06-2014
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Looking for formula to return a date that falls between 2 dates

    I'm looking for a formula that will return "PY" if dates fall between 10/31/2012 and 11/1/2013

    I've attached the example, highlighted in yellow is what I want to be the result.

    Thanks
    Attached Files Attached Files
    Last edited by cobo1981; 03-13-2014 at 11:48 AM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    try =IF(AND(B5>40908,C5<41579),"PY","OLD")
    in col E

    EDIT, oops, had the first date wrong, change 40908 to 41213. And not sure if you actually want it in col A since your request is a bit unclear.
    Last edited by Sam Capricci; 03-13-2014 at 11:51 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    03-06-2014
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Looking for formula to return a date that falls between 2 dates

    Well let me put it this way, I would like to have formula display PY and CY, not the OLD, I would like OLD to be left out. The location of the formula really does not matter, I can figure that out if I have the right formula.

    BTW you can only concentrate on column C, I don't know why I included column B.
    Last edited by cobo1981; 03-13-2014 at 12:03 PM.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    I probably should have asked more questions. So is col B (service start) irrelevant to the formula you want?

  5. #5
    Registered User
    Join Date
    02-08-2012
    Location
    Asia
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5

    Re: Looking for formula to return a date that falls between 2 dates

    If you are sure that there won't be records falling under the range for OLD going forward, just replace the OLD to CY in the formula.

    Please Login or Register  to view this content.
    Or as a precaution, we can have all 3

    Please Login or Register  to view this content.
    Last edited by airbornex; 03-13-2014 at 12:17 PM. Reason: Updated date value

  6. #6
    Registered User
    Join Date
    03-06-2014
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Looking for formula to return a date that falls between 2 dates

    airbornex that worked perfectly, thanks all for your help.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    Just so you know, the 40908 in Airbornex's formula is for 1/1/2012 and not 11/1/2012 so you might want to change that.

  8. #8
    Registered User
    Join Date
    02-08-2012
    Location
    Asia
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5

    Re: Looking for formula to return a date that falls between 2 dates

    Quote Originally Posted by Sambo kid View Post
    Just so you know, the 40908 in Airbornex's formula is for 1/1/2012 and not 11/1/2012 so you might want to change that.
    Oops, didn't check since I just borrowed and edit from yours. Thanks for pointing it out.

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    sorta thought that might be what you might have done so that is why i pointed it out. Especially since I didn't think you could also mistype with the same wrong date I did.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    This is how i would have done it had the OP not yet had their answer, but I'll post it any way since it is still different from yours and what I posted earlier...
    =IF(AND(B2>41213,C2<41579),"PY",IF(OR(B2>41578,C2>41578),"CY","")) in col A dragged down.

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Looking for formula to return a date that falls between 2 dates

    BTW Cobo, if your problem is solved please mark your post as solved using thread tools at the top and you can thanks ALL or any of those who helped by clicking on "* Add reputation" as that is how we advance on this forum.

  12. #12
    Registered User
    Join Date
    03-06-2014
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Looking for formula to return a date that falls between 2 dates

    Well I'm using cells as dates as they will change in the future so airbornex's little mistake will have no effect.
    Sambo thanks for pointing out about the tools and rep

+ 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. Replies: 4
    Last Post: 06-28-2013, 08:41 AM
  2. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  3. Replies: 3
    Last Post: 10-13-2012, 08:09 PM
  4. [SOLVED] Return value in X if date in Y falls between two dates - Help!
    By nephilim3uk in forum Excel General
    Replies: 4
    Last Post: 07-08-2012, 10:13 AM
  5. Return a specified date when it falls within a range....
    By Nokose451 in forum Excel General
    Replies: 1
    Last Post: 01-16-2006, 06:10 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