Date.DaysInMonth
DateReturns the number of days in the month of the given date.
Syntax
Date.DaysInMonth(dateTime as any) as nullable numberParameters
| Name | Type | Required | Description |
|---|---|---|---|
dateTime | any | Yes | A date, datetime, or datetimezone value. |
Return Value
number — The number of days in the month (28, 29, 30, or 31).
Remarks
Date.DaysInMonth returns the number of days in the month containing the given date, datetime, or datetimezone value. Possible return values are 28, 29, 30, or 31. February returns 28 in common years and 29 in leap years. This is useful for computing month-end dates, prorating amounts, or building dynamic date calendars. If the input is null, the function returns null.
A common use case is computing what fraction of a month an event covers (e.g., for proration). You can divide the number of active days by Date.DaysInMonth to get the fractional month weight. Another use case is constructing the last day of a month: #date(Date.Year(d), Date.Month(d), Date.DaysInMonth(d)), though Date.EndOfMonth is more concise for that purpose.
Note that Date.DaysInMonth requires any date within the target month — it does not matter if you pass the 1st, 15th, or last day; the result is the same for all dates in that month.
Examples
Example 1: Count days in each order month
Table.AddColumn(
Table.SelectColumns(
Table.FirstN(Sales, 5),
{"OrderID", "OrderDate"}
),
"DaysInMonth", each Date.DaysInMonth([OrderDate]), Int64.Type
)OrderID | OrderDate | DaysInMonth | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | 31 |
| 2 | 2 | 1/18/2024 | 31 |
| 3 | 3 | 2/1/2024 | 29 |
| 4 | 4 | 2/10/2024 | 29 |
| 5 | 5 | 3/5/2024 | 31 |
Example 2: Days in February for a leap year vs. common year
#table(
type table [Year = number, Date = date, DaysInFeb = number],
{
{2024, #date(2024, 2, 1), Date.DaysInMonth(#date(2024, 2, 1))},
{2023, #date(2023, 2, 1), Date.DaysInMonth(#date(2023, 2, 1))}
}
)Year | Date | DaysInFeb | |
|---|---|---|---|
| 1 | 2,024 | 2/1/2024 | 29 |
| 2 | 2,023 | 2/1/2023 | 28 |
Example 3: Prorate a monthly amount by days active
Table.AddColumn(
Table.SelectColumns(Table.FirstN(Sales, 5), {"OrderID", "OrderDate", "UnitPrice"}),
"ProratedPrice",
each
let
daysInMonth = Date.DaysInMonth([OrderDate]),
dayOfMonth = Date.Day([OrderDate]),
remainingDays = daysInMonth - dayOfMonth + 1
in
Number.Round([UnitPrice] * remainingDays / daysInMonth, 2),
type number
)OrderID | OrderDate | UnitPrice | ProratedPrice | |
|---|---|---|---|---|
| 1 | 1 | 1/15/2024 | 25 | 13.71 |
| 2 | 2 | 1/18/2024 | 50 | 22.58 |
| 3 | 3 | 2/1/2024 | 15 | 15 |
| 4 | 4 | 2/10/2024 | 75 | 55.17 |
| 5 | 5 | 3/5/2024 | 25 | 21.77 |