Finding if a date is on a range of two weekends between another date

excel if date is between two dates return value
return value if date within range
excel if date range falls within date range
check if date is between two dates javascript
index match between two dates
vlookup between two dates and return corresponding value with lookup formula
excel if date is between multiple date ranges
excel countif between two dates

I'm making a application that give discounts for users and one of the discounts is given on his birthday. As is really hard to use a discount only on your birthday, we decide to give the discount all the week, including both weekends around.

Some rules
  • Given the birth date, the discount will start on the previous friday of the birthday;
  • The discount will end 10 days after the start, on the sunday;
  • If the birthday it's on a friday, still get the last friday before this one;

Ok, so we need a method that receive the birthdayDate and the todayDate and return true or false telling if the date is on the birthday range.

The problem

Everything seens fine until you start to look at the dates near the change of the year. If your birthday is on December 31 of 2018, you can use the discount on January 6 of 2019, the same way, if your birthday is on January 1 of 2019, on December 28 of 2018 you already can use the discount. So look only to the birthday of current year is not enough and, as the days of the week change every year, the last friday of your birthday this year will not be same day on the next one.

There is a elegant way to easily find this range and create the method returning true or false?

The real problem is find which birthday is relevant birthday, as you can receive the discount after your last birthday and before your next birthday.

The original code is in PHP, but as it's a algorithm problem, I can accept answers in any language

Test cases
| Today             | Birth             | Output |
|-------------------|-------------------|:------:|
| February 15, 2019 | February 22, 2000 | true   |
| February 24, 2019 | February 22, 2000 | true   |
| February 25, 2019 | February 22, 2000 | false  |
| December 28, 2018 | January 03, 2000  | true   |
| December 27, 2018 | January 03, 2000  | false  |
| December 27, 2019 | January 03, 2000  | true   |
| January 01, 2019  | January 03, 2000  | true   |
| January 01, 2019  | December 31, 2000 | true   |
| January 01, 2019  | December 28, 2000 | false  |

How to determine if a date falls between two dates or on weekend in , Determine if a date falls on a weekend with formulas and VBA code Select the range with dates you want to determine if they fall between two dates, and we will show you methods of comparing dates if greater than another date in Excel. Determine if a date falls between two dates in Excel with Kutools for Excel. Here recommend you an amazing tool - the Select Specific Cells utility of Kutools for Excel.This tool helps you not only find out all dates that are falling between two given dates, but also select all qualified date cells immediately.

proposal solution (using JS and timestamps)

// Helper constants
const day = 1000 * 60 * 60 * 24
const friday = 5
const $input = document.getElementById('datepicker')
const $result = document.getElementById('result')
const $tests = document.getElementById('tests')

// Generate the period based on birthday and now (optional)
function getPeriod(birthday, today) {
  const now = new Date(today || new Date())
  // reset to start at 00:00
  now.setHours(0)
  now.setMinutes(0)
  now.setMilliseconds(0)

  // Available beggining date
  const begin = new Date(now - (10 * day))
  birthday = new Date(birthday)
  // fix birthday year
  birthday.setFullYear(begin.getFullYear())
  
  // if it already passed, jump to next year
  if (birthday < begin)
    birthday.setFullYear(birthday.getFullYear() + 1)

  // Start date
  const start = new Date(birthday)
  
  // if the birthday is already on friday, jump to previous friday (3th condition) 
  if(start.getDay() === friday)
    start.setTime(start.getTime() - (day * 7))

  // go to the last friday
  while (start.getDay() !== friday) 
    start.setTime(start.getTime() - day)
  // return found date + 10 days
  return [start, new Date(start.getTime() + (10 * day)-1)]
}


function calculatePeriod() {
  const birthday = $input.value
  const [begin, end] = getPeriod(birthday)
  $result.innerHTML = begin.toString() + '<br>' + end.toString()
}


const testCases = [
  ['February 15, 2019', 'February 22, 2000'],
  ['February 24, 2019', 'February 22, 2000'],
  ['February 25, 2019', 'February 22, 2000'],
  ['December 28, 2018', 'January 03, 2000'],
  ['December 27, 2018', 'January 03, 2000'],
  ['December 27, 2019', 'January 03, 2000'],
  ['January 01, 2019 ', 'January 03, 2000'],
  ['January 01, 2019 ', 'December 31, 2000'],
  ['January 01, 2019 ', 'December 28, 2000'],
]

testCases.map(([now, birthday]) => {
  const [begin, end] = getPeriod(birthday, now)
  $tests.innerHTML += `BIRTH: ${birthday}<br>NOW:   ${now}<br>BEGIN: ${begin}<br>END  : ${end}<br><br>`
})
<h3>Select an date</h3>
<input type="date" id="datepicker" value="2019-01-01"/>
<button onclick="calculatePeriod()">Calculate</button>

<p>Result: <pre id="result">...</pre></p>
<hr />

<h3>Tests</h3>
<pre id="tests"></pre>

How to Calculate Number of Weekends between Two Dates in Excel , How to do it. You can use a formula based on the NETWORKDAYS function and the DAYs function to calculate the number of weekends between two given dates. Type this formula into cell C2, and press Enter key on your keyboard, and then copy this formula from cell C2 to range C3:C4 to apply this formula. Use the NETWORKDAYS.INTL function when you want to calculate the number of workdays between two dates. You can also have it exclude weekends and holidays too. Before you begin: Decide if you want to exclude holiday dates. If you do, type a list of holiday dates in a separate area or sheet. Put each holiday date in its own cell.

Here the same solution from Jon Skeet, but in PHP using Carbon.

use Carbon\Carbon;

class UserBirthdayTest extends TestCase
{
    /**
     * Setup this test
     */
    public function setUp()
    {
        parent::setUp();
    }

    /**
     * Test create methods on basic CRUDs controllers
     */
    public function testCreate()
    {
        $this->validate("2019-02-15", "2000-02-22", true);
        $this->validate("2019-02-24", "2000-02-22", true);
        $this->validate("2019-02-25", "2000-02-22", false);
        $this->validate("2018-12-28", "2000-01-03", true);
        $this->validate("2019-01-01", "2000-01-03", true);
        $this->validate("2018-12-27", "2000-01-03", false);
        $this->validate("2019-12-27", "2000-01-03", true);
    }

    /**
     * @param \PHPUnit\Framework\TestResult $today
     * @param $birthday
     * @param $expectedResult
     * @return \PHPUnit\Framework\TestResult|void
     */
    public function validate($today, $birthday, $expectedResult)
    {
        $today = new Carbon($today);
        $birthday = new Carbon($birthday);

        // closest discount period
        $threeMonthsAgo = (new Carbon($today))->subMonth(3);

        $ageThreeMonthsAgo = $birthday->diffInYears($threeMonthsAgo);

        // Note: this will use Feb 28th for a Feb 29th birthday in a non leap year.
        $relevantBirthday = $birthday->addYears($ageThreeMonthsAgo + 1);

        // Find the strictly-previous Friday to start the discount interval
        $discountStart = Carbon::createFromTimestamp(strtotime('last friday', $relevantBirthday->timestamp));
        $discountEndInclusive = (new Carbon($discountStart))->addDays(9);

        $actualResult = $today->greaterThanOrEqualTo($discountStart) && $today->lessThanOrEqualTo($discountEndInclusive); // between($discountStart, $discountEndInclusive, false);

        $this->assertEquals($expectedResult, $actualResult);
    }
}

How To Test If A Date Is On A Weekend, The WEEKDAY(Date) function will return a number from 1 to 7 depending on Returns 1 when the date is on a Sunday; Returns 2 when the date is on a To find the weekend we need to test if WEEKDAY(Date) equals 1 or 7 if the Date is on a Saturday; {FALSE,FALSE} if the Date is any other weekday. Used below logic to calculate the no of Saturdays or Sundays between a start date and end date. CREATE FUNCTION dbo.WEEKEND_COUNT ( @Start_Date datetime, @End_Date datetime ) RETURNS int AS BEGIN Declare @count int = 0; while @Start_Date<=@End_Date Begin IF DatePart(WEEKDAY,@Start_Date) = 1 or DatePart(WEEKDAY,@Start_Date) = 7 SET @count=@count+1 SET @Start_Date=DateAdd(d,1,@Start_Date) END

How to use the Excel WORKDAY function, How to use the Excel WORKDAY function to Get a date n working days in the future or past. WORKDAY can be used to calculate due dates, delivery dates, and other dates that If you need to customize which days of the week are considered weekend days, use the Excel formula: Random date between two dates. Another common calculation is to calculate the difference between two dates such as a date received or date started and the actual completion date. While these basic calculations may be helpful for some projects, they don’t exclude weekends.

Excel formula: Highlight dates that are weekends, For example, if you have dates in the range C4:C10, and want to weekend dates, select the range C4:C10 and create a new conditional formatting rule that uses� The Duration Calculator calculates the number of days, months and years between two dates.

Excluding weekends and holidays in date differences in PowerApps, Excluding weekends and holidays in date differences in PowerApps if the start and end dates are between Monday and Friday (weekdays from 2 to 6), CountIf function to determine how many of those holidays fall within our date range:. There are more details on how to get the month part of any date field here. Between two date ranges Now let us move to select a range of records between two dates. Here is the SQL for this SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31' Date Format to use in query You have seen we have used 'Y-m-d' date format in our query

Comments
  • It sounds like it's simply "Find the previous Friday before the relevant birthday." That's pretty easy to do in many languages. Could you show what you've already tried? I don't see why dates near the change of year would actually be particularly relevant here. If it's to do with which birthday is the relevant birthday, it would be helpful if you could be more specific about this. It would be great if you could list all the inputs (e.g. current date, birthday) and expected output.
  • Find the last friday is really simple, and most of the languages already have a method for it. The problem is: which birthday you need to find the last friday? Maybe your next birthday is already giving you the discount. Maybe is the last one. I'll add more cases to the question.
  • It's hard to know what you mean by "which birthday you need" when we don't know what the expected output is. Are you trying to find out whether the discount should be valid today? Or a period to show a user? If you could pin this down to a specific language with a minimal reproducible example that shows what you've tried so far, it would be easier to help you.
  • I've added some test cases. I'm reviewing the text to make it more clear.
  • Okay, those test cases definitely make it easier to help. I'll write up an answer using C# as the implementation language, but explain what it's doing too.