0

Hi, new to Ninox, trying to count number of Mondays, Tuesdays, etc each month

Hi, I'm new to Ninox. I am trying to build a system that counts the number of each day of the week in each month. Example:

July 2024

Mondays: 5

Tuesdays: 5

Wednesdays: 5

Thursdays: 4

Fridays: 4

Saturdays: 4

Sundays: 4

It should do this for every month one month ahead of the current month. I'm not really sure where to start. Can anyone help?

8 replies

null
    • John_Halls
    • 3 mths ago
    • Reported - view

    I would tackle this by finding out how many days there are in the month (variable c) and what day of the week is the first of the month (variable d).

    let a := today();
let b := date(year(a),month(a)+1,1)
let c := days(b,date(year(a),month(a)+2,1));
let d := weekday(b)

    Apart from non-leap year February's this day will be a 5, plus the next day for 30 day months, plus the next day for 31 day months. You want to loop through the days c - 28 attributing 5 to those days from d onwards. How do you want this represented? Is it a block of text, or records to be created / updated?

    Regards John

    • John_Halls
    • 3 mths ago
    • Reported - view

    Here's a function to return the number of a particular day of the week (where Monday is 0 and Sunday is 6, for the month of a given date

    function countDays(dayOfTheWeek : number,dateToCheck : date) do
    let a := days(date(year(dateToCheck), month(dateToCheck), 1), date(year(dateToCheck), month(dateToCheck) + 1, 1));
    let b := weekday(date(year(dateToCheck), month(dateToCheck), 1));
    let c := (dayOfTheWeek - b + 8) % 7;
    let d := a - 28;
    if c > d then 4 else 5 end
end

    Thus countDays(0, today()) give 4. There are 4 Mondays in August 2024

      • red_kite
      • 3 mths ago
      • Reported - view

       What a script sugar. 👍

    • Sam_Villano
    • 3 mths ago
    • Reported - view

    Wow, thank you! What we're ultimately trying to do is calculate the value ($) of a job. We charge a flat monthly rate for our subscriptions, and the service is once every week on the same day of the week. So, in some months with 4 services (4 Mondays for example), the service would be worth $50 if we charge $200 per month for the service. In months with 5 services (5 Mondays), each service would be worth $40.

    We would like to have a field in each job record called ServiceValue that calculates the value of the service.

    Part of it would be based on price as well, as we have three tiers, 30 minute tier, 45 minute tier, and 60 minute tier.

    When I pasted what you wrote above in a formula field, it returned blank on the record. I'm assuming my very basic programming skills means I'm missing something obvious. I tried these two methods but got no return sadly:

    LessonDate is equal to 2024-07-31

      • Fred
      • 3 mths ago
      • Reported - view

      The code wrote that begins with function goes into the Global Function.

      1) open your DB

      2) go to admin mode

      3) click on Options

      4) under Global functions paste the code

      5) click Save

      You have created a new function called countDays that you can call anywhere in your DB. You have to provide a number from 0 - 6 (representing each day of the week starting with Monday as 0) and a date (this example uses the today() function, but you can use any date by typing in: date(yyyy,mm,dd).

      Now open your table and go to your formula field and type in what John wrote:

      countDays(0, today())

      This should give you 4 Mondays in August 2024 (today).

      If you want to count Tuesdays in December of 1945 it would look like:

      countDays(1,date(1945,12,01))
    • John_Schaffer
    • 3 mths ago
    • Reported - view

    This is an interesting way to calculate a service.  Just a question: isn’t the rendered service the same regardless of which day of the week it is performed?  My first thought was that the 5th-day-of-the-month service would actually be a lesser service.  Or is this a way of cost accounting for a repetitive monthly subscription?  Not trying to be argumentative, just to understand.

    • Ninox partner
    • RoSoft_Steven.1
    • 3 mths ago
    • Reported - view

    Hi ,

    You could use this for the remaining weekdays of the month:

    let firstday := date(year(Date), month(Date), 1);
let lastday := date(year(Date), month(Date) + 1, 0);
let calendardays := days(firstday, lastday);
let c := 0;
for dd in range(0, calendardays + 1) do
    let dat := firstday + dd;
    if weekday(dat) = 0 and dat > Date then
        c := c + 1
    end
end;
"mondays left this month : " + c

    change line 7 to: if weekday(dat) = 1 and dat > Date then

    to check the remaining tuesdays of the month.(and so on...)

    change line 7 to: if weekday(dat) = 1 then

    for the total amount of tuesdays in the month.

    Date is a date-field BTW

    • Ninox partner
    • RoSoft_Steven.1
    • 3 mths ago
    • Reported - view

    date(yyyy,mm,0) gives the last day of the previous month of mm

Login to reply

Content aside

  • 3 mths agoLast active
  • 8Replies
  • 65Views
  • 6 Following