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

1. ## 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. ## 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

3. ## 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 ?

4. ## 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. ## Re: Date formula to return a specific date if todays date is between a date range

@Aritase
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.

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

#### 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