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

張良仲 

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

張良仲 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

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