Downloads
Power Query MDateBeginner

Date.DaysInMonth

Returns the number of days in the month that contains a date.

Syntax
Date.DaysInMonth(dateTime as any) as number

Returns: The number of days, from 28 to 31, as a number.

What it does

Date.DaysInMonth returns how many days the month of a date has. Finance uses it to prorate a monthly amount to a daily rate, such as a monthly subscription spread across the days it covers. February gives 28 or 29 depending on the year, and the function applies that rule.

Syntax and arguments

Example

A software subscription costs 3,100 dollars a month. A new customer starts on February 10, 2028, and the first month is charged by the day. The daily rate uses the days in February 2028, which is a leap year.

let
    MonthlyFee = 3100,
    StartDate = #date(2028, 2, 10),
    DaysInMonth = Date.DaysInMonth(StartDate),
    DailyRate = MonthlyFee / DaysInMonth,
    DaysCharged = Duration.Days(Date.EndOfMonth(StartDate) - StartDate) + 1,
    FirstMonth = Number.Round(DailyRate * DaysCharged, 2)
in
    {DaysInMonth, FirstMonth}

February 2028 has 29 days, so the daily rate is 3,100 divided by 29. Counting from February 10 to the end of the month gives 20 days, so the first month is about 2,137.93 dollars. The result is rounded to cents at the end, not at each step.

Example: a second, different use

Work out the daily rate for an accrual in a 30 day month, to book a partial month of expense for a contract that ends mid-month.

Things to watch

In business terms

Prorations for mid-month starts and stops, daily accruals and per diem costs all use the days in the month. Using the actual count keeps the charge fair to the customer and matches the expense to the days it covers.

Related functions

All Power Query M functions · View as Markdown · M basics