Count Number of Mondays in a Given Date Range

Count number of Mondays in a given date range

Try this:

static int CountDays(DayOfWeek day, DateTime start, DateTime end)
{
    TimeSpan ts = end - start;                       // Total duration
    int count = (int)Math.Floor(ts.TotalDays / 7);   // Number of whole weeks
    int remainder = (int)(ts.TotalDays % 7);         // Number of remaining days
    int sinceLastDay = (int)(end.DayOfWeek - day);   // Number of days since last [day]
    if (sinceLastDay < 0) sinceLastDay += 7;         // Adjust for negative days since last [day]

    // If the days in excess of an even week are greater than or equal to the number days since the last [day], then count this one, too.
    if (remainder >= sinceLastDay) count++;          

    return count;
}

How to find number of Mondays or any other weekday between two dates in Python?

This code still uses a for loop and an if/else.

import datetime
import calendar

def weekday_count(start, end):
  start_date  = datetime.datetime.strptime(start, '%d/%m/%Y')
  end_date    = datetime.datetime.strptime(end, '%d/%m/%Y')
  week        = {}
  for i in range((end_date - start_date).days):
    day       = calendar.day_name[(start_date + datetime.timedelta(days=i+1)).weekday()]
    week[day] = week[day] + 1 if day in week else 1
  return week

print(weekday_count("01/01/2017", "31/01/2017"))

# prints result
# {'Monday': 5, 'Tuesday': 5, 'Friday': 4, 'Wednesday': 4, 'Thursday': 4, 'Sunday': 5, 'Saturday': 4}

Get number of monday in a rangedate mysql

You can use a solution using a calendar table. So you can use a solution like the following:

1. create a table with calendar data

-- create the table "calendar"
CREATE TABLE `calendar` (
  `dateValue` DATE
);

-- insert the days to the table "calendar"
INSERT INTO calendar
SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
HAVING gen_date BETWEEN '2019-01-01' AND '2019-12-31'

You can find the script to generate the calendar data on StackOverflow:

How to populate a table with a range of dates?

2. create the table with your data (with monday tarif)

-- create the table "tarif"
CREATE TABLE tarif (
  tarif_id INT(11) NOT NULL AUTO_INCREMENT,
  start_tarif DATE NOT NULL,
  end_tarif DATE NOT NULL,
  day_tarif VARCHAR(50) NOT NULL,
  monday_tarif VARCHAR(50) NOT NULL,
  PRIMARY KEY (tarif_id)
);

-- insert the tarif information
INSERT INTO tarif VALUES 
  (1, '2019-02-01', '2019-02-10', '10', '5'),
  (2, '2019-02-11', '2019-02-20', '20', '5'),
  (3, '2019-02-21', '2019-02-28', '10', '5'),
  (4, '2019-03-01', '2019-02-10', '15', '5');

Note: To create a useful example I added the column monday_tarif and insert the value 5 on every date range.

3. get the result

Now you can get all days of your needed range (between 2019-02-05 and 2019-02-21) from the calendar table. With a LEFT JOIN you add your tarif table to all days of date range.

With a CASE WHEN and the condition DAYOFWEEK = 2 or DAYNAME = 'Monday' you can check if the current date is a Monday or not, to get the correct tarif value of the day.

SELECT SUM(CASE WHEN DAYOFWEEK(cal.dateValue) = 2 THEN tarif.monday_tarif ELSE tarif.day_tarif END) AS sumWithMondayTarif 
FROM calendar cal
    LEFT JOIN tarif ON cal.dateValue BETWEEN start_tarif AND end_tarif
WHERE cal.dateValue BETWEEN '2019-02-05' AND '2019-02-21';

---

You can also use a SELECT with a sub select of the calendar:

SELECT SUM(CASE WHEN DAYOFWEEK(cal.dateValue) = 2 THEN tarif.monday_tarif ELSE tarif.day_tarif END) AS sumWithMondayTarif FROM (
  SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) dateValue FROM
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
  HAVING dateValue BETWEEN '2019-02-05' AND '2019-02-21'
) cal LEFT JOIN tarif ON cal.dateValue BETWEEN start_tarif AND end_tarif

---

demo on dbfiddle.uk

Calculate number of mondays in google spreadsheet

You may try FILTER() function.

=COUNT(FILTER(A2:A7,WEEKDAY(A2:A7)=2))

Or direct day name.

=COUNT(FILTER(A2:A7,TEXT(A2:A7,"dddd")="Monday"))

Calculate number of specific weekdays between dates

How to count specific days of the week between two dates in O(1):

// days is an array of weekdays: 0 is Sunday, ..., 6 is Saturday
function countCertainDays( days, d0, d1 ) {
  var ndays = 1 + Math.round((d1-d0)/(24*3600*1000));
  var sum = function(a,b) {
    return a + Math.floor( ( ndays + (d0.getDay()+6-b) % 7 ) / 7 ); };
  return days.reduce(sum,0);
}

Example on counting Mondays, Wednesdays, and Fridays [1,3,5] between two dates:

countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,1)) // 1
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,2)) // 1
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,3)) // 2
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,4)) // 2
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,5)) // 3
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,6)) // 3
countCertainDays([1,3,5],new Date(2014,8,1),new Date(2014,8,7)) // 3

Note that the month parameter to Date is 0-based, so 1 Sept 2014 is Date(2014,8,1).

how to find number of mondays or tuesdays between two dates?

You could create a function that uses strtotime() recursively to count the number of days. Since strtotime("next monday"); works just fine.

function daycount($day, $startdate, $counter)
{
    if($startdate >= time())
    {
        return $counter;
    }
    else
    {
        return daycount($day, strtotime("next ".$day, $startdate), ++$counter);
    }
}

echo daycount("monday", strtotime("01.01.2009"), 0);

Hopefully this is something you're looking for :)

---

Related Topics