+ Reply to Thread
Results 1 to 7 of 7

Returning a value if cell date is between two dates

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Returning a value if cell date is between two dates

    hi.

    kindly advise on how to return a text value if cell date is between 2 dates.

    example, if cell date is between 27oct13-29mar14, i shud get a return
    txt 'w13'. if cell date is between 30mar14-25oct14, i shud get a return
    txt 's14'.

    thank you: -nik-

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Returning a value if cell date is between two dates

    try =IF(AND(A1>=41574,A1<=41724),"w13",IF(AND(A1>=41728,A1<=41937),"s14",""))
    where your dates are in col A.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Returning a value if cell date is between two dates

    =IF(AND(A1>"27oct13"+0,A1<="29mar14"+0),"w13",IF(AND(A1>="30mar14"+0,A1<="25oct14"+0),"s14",""))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    06-13-2013
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Returning a value if cell date is between two dates

    many thanks for your swift replies, samdo kid & martidwilson.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning a value if cell date is between two dates

    Use cells to hold the date ranges:


    Data Range
    A
    B
    C
    D
    E
    1
    10/27/2013
    3/29/2014
    ------
    10/27/2013
    w13
    2
    3/30/2014
    10/25/2014


    This formula entered in E1:

    =IF(AND(D1>=A1,D1<=B1),"w13",IF(AND(D1>=A2,D1<=B2),"s14",""))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    06-13-2013
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Returning a value if cell date is between two dates

    thank you, tony.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning a value if cell date is between two dates

    You're welcome!

+ 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. Returning a value if cell date is between two dates
    By joeljoel in forum Excel General
    Replies: 8
    Last Post: 03-24-2016, 10:30 AM
  2. [SOLVED] Returning a date from a range if date falls between two other dates
    By sdavison in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2013, 03:59 AM
  3. [SOLVED] Returning a date based on a range of dates
    By ExcelAteMyHomework in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-10-2012, 06:07 PM
  4. Replies: 10
    Last Post: 05-13-2012, 07:43 PM
  5. returning a value if a date falls between two dates
    By chillangella in forum Excel General
    Replies: 3
    Last Post: 09-28-2009, 06:51 AM

Tags for this Thread

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