+ Reply to Thread
Results 1 to 5 of 5

Date formula to return a specific date if todays date is between a date range

  1. #1
    Registered User
    Join Date
    09-05-2011
    Location
    Ryther, England
    MS-Off Ver
    Office 365
    Posts
    63

    Date formula to return a specific date if todays date is between a date range

    Hi there. Im looking for some help with a date formula that i need to return a specific date in the future if todays date falls within a date range. In fact there are 3 varients to the request.
    *
    Formula 1
    ************************************************************************************************ If today's date is between 1st and 20th of month return a date that is 1st of next month i.e.
    ***********************************************************************************************tToday's date - 17-Apr-18

    Return date - 01-May-18

    Formula 2********************************************************

    If today's date is between 20th and end of month return a date that is 1st of next but one month i.e.******************************************************************************************************************
    Today's date - 23-Apr-18

    Returned date - 01-Jun-18

    Formula 3*****************************************************************

    The returned date needs to be the 1st of the next available month that is 28 days or more away from todays date i.e.:

    ***********************************************************************************************TToday's date - 23 April + 28 days = 21 May therefore next available month is June

    Returned date - 01-Jun-18

    Any help greatly appreciated :-)

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Date formula to return a specific date if todays date is between a date range

    1
    =IF(AND(DAY(A1)>0,DAY(A1)<21),EOMONTH(A1,0)+1,""))
    2
    =IF(DAY(A1)>20,EOMONTH(A1,1)+1,"")
    3
    =EOMONTH(A1+28,0)+1

    If you wanted to combine the first 2
    =IF(AND(DAY(A1)>0,DAY(A1)<21),EOMONTH(A1,0),EOMONTH(A1,1))+1
    Last edited by kev_; 03-21-2018 at 07:57 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Date formula to return a specific date if todays date is between a date range

    this covers the 1st 2
    =IF(AND(DAY(TODAY())>=1,DAY(TODAY())<=20), DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(1)),DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(1)))

    But when does the 3rd formula apply ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    03-20-2018
    Location
    Norway
    MS-Off Ver
    2018
    Posts
    6

    Re: Date formula to return a specific date if todays date is between a date range

    Hello. I'm looking for something very similar as OP requested. Figured I could use the same thread if possible?
    (Maybe you already answered my question, if you did. Could you add a quick description of what formula 1 does?

    My request would be this:
    If the date is between 21.01.2018 - 31.01.2018, then A7+7. If the date is between 01.02.2018 - 08.02.2018, then A6+7

    Or perhaps like this:
    If the date is 01.02.2018 or higher, then A6+7, if the date is 31.01.2018 or lower, then A7+7
    Either one of these two will work

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Date formula to return a specific date if todays date is between a date range

    @Aritase
    - please start a new thread
    - answering 2 different questions on the thread becomes confusing
    thanks
    Extract from the forum rules
    2. Don't post a question in the thread of another member - start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

+ 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: 9
    Last Post: 10-08-2015, 01:50 PM
  2. Replies: 5
    Last Post: 01-05-2015, 03:57 PM
  3. [SOLVED] look-up a date in a range and find the date that is closest todays date
    By VanShark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2014, 09:46 PM
  4. [SOLVED] If formula, if two cells are blank, return blank, if one has a date, subtract todays date
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 12:47 PM
  5. Replies: 2
    Last Post: 04-08-2012, 03:36 AM
  6. Using VB to find a range based on todays date and todays date +30
    By Steve_al in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 09:31 AM
  7. Date Function formula that will return the date of a specific week
    By Greg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2006, 12: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