0

The number of days in each month of the day period and its total

Suppose I want to know how to calculate the number of days in each month and their totals during the period from 8/1 to 9/10 Thank you

9 replies

null
    • paul_chappell.1
    • 1 yr ago
    • Reported - view

    days(‘Date From’,’Date To’)+1

    • cyan_silver
    • 1 yr ago
    • Reported - view

    Thank you, Paul.
    Sorry my question was not written very clearly, I made a simple example, I don't know if there is an easier way to achieve a similar answer, thanks

      • Paul_Chappell
      • 1 yr ago
      • Reported - view

      張良仲 

      Thank you for the example.

      I'm still not quite sure what you are trying to. 

      If you are entering two dates and need the number of days between them, then you use:

      let NumberOfDays:=days(‘start date’,’end date’)+1

      If you want to know the total number of days from the first day of the 'start date' to the last day of the 'end date' then you can do this:

      The first day of any month is obviously the first (01):

          let FirstDayOfMonth:=date(year('start date'), month('start date'), 1)

      The last day of a month can change, so we just take the first of the following month and deduct a day:

          let LastDayOfMonth:=date(year('start date'), month('start date')+1, 1)-1

      or this does the same thing:

           let LastDayOfMonth:=date(year('start date'), month('start date') + 1, 0)

      You don't need to worry about the year as this changes automatically.

      To calculate the number of days in any given month:

      let DaysInMonth:=days(FirstDayOfMonth, LastDayOfMonth) + 1

      So, to calculate the total number of days in all the months from 'start date' to 'end date', use a combination of the above formulas to work out the first day of the 'start date' and last day of the 'end date' and calculate the number of days between them:

      let FirstDayOfFirstMonth:=date(year('start date'), month('start date'), 1);

      let LastDayOfLastMonth:=date(year('end date'), month('end date')+1, 0);

      let TotalDays:=days(FirstDayOfFirstMonth, LastDayOfLastMonth) + 1

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • Reported - view

    Paul Chappell

    Hi Paul 

    Your formulas don't give any result, after include the Formula at the end of same formula include void and any result. 

    (let LastDayOfMonth := date(year(Fecha_Real), month(Fecha_Real) + 1, 1) - 1;
    void)

      • Paul_Chappell
      • 1 yr ago
      • Reported - view

      Rafael 

      That's right.

      The formulas are not used in isolation.  Clearly, you have to show a resulting variable.

      let FirstDayOfFirstMonth:=date(year('start date'), month('start date'), 1);

      let LastDayOfLastMonth:=date(year('end date'), month('end date')+1, 0);

      let TotalDays:=days(FirstDayOfFirstMonth, LastDayOfLastMonth) + 1

      TotalDays

       

      I was assuming this was obvious.  I shouldn't assume.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Paul Chappell 

      Yes very sorry 😖 and Thanks 

    • cyan_silver
    • 1 yr ago
    • Reported - view

    Thank you, Paul.
    What I want to know is the number of days in each month between August 5th and November 20th.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      張良仲 Something like that?

      Here is the formula I used:

      // create a table that contains all the dates
      var t := for i from 0 to days('Start date', 'End date') do
              {
                  year: year('Start date' + i),
                  month: month('Start date' + i),
                  day: day('Start date' + i)
              }
          end;
      
      // research of each year
      for y in unique(t.year) do
          // Search of each month for the year y
          for m in unique(t[year = y].month) do
              Counting the number of days in month m and year y
              format(date(number(y), number(m), 1), "YYYY MM : ") + count(t[year = y and month = m]) + " days
      "
          end
      end
      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Jacques TUR  There was a defect in the calculation of the dates. Because of a bad rounding of the dates and the independent time zone option, I end up with an aberrant calculation when I add days to a date. For example, if the independent time zone option is not activated, adding 86 days or 87 days to the date 2022/8/5 gives the same result, i.e. 2022/10/30.

      As a result, the table of days has the same date twice: 

      ...
      {"year":2022,"month":10,"day":29}
      {"year":2022,"month":10,"day":30}
      {"year":2022,"month":10,"day":30}
      {"year":2022,"month":10,"day":31}
      {"year":2022,"month":11,"day":1} 
      ...

      It is therefore best to use the date creation functions for each calculation, even if it takes longer to write.

      var y := year('Start date');
      var m := month('Start date');
      var d := day('Start date');
      var t := for i from 0 to days('Start date', 'End date' + 1) do
              {
                  year: year(date(y, m, d + i)),
                  month: month(date(y, m, d + i)),
                  day: day(date(y, m, d + i))
              }
          end;
      for y in unique(t.year) do
          for m in unique(t[year = y].month) do
              format(date(number(y), number(m), 1), "YYYY MM : ") + count(t[year = y and month = m]) + " days
      "
          end
      end
      

      With this new formula, the number of days is now correct:

      2022 08 : 27 days ,
      2022 09 : 30 days ,
      2022 10 : 31 days ,
      2022 11 : 20 days

      I don't know about you, but I'm going to check the date calculations I have in my databases... it's chilling 😳

Content aside

  • 1 yr agoLast active
  • 9Replies
  • 177Views
  • 5 Following