Hi everyone,
To make it easier to find I’ve placed a few methods to count the number of days between two dates in Power Query, here, in the M Code Showcase category. The first piece of M code generates a table containing two columns, you can use for practice - just paste that into a new blank query.
let
Source = #table( type table[StartDate=date, EndDate=date],
{
{ #date(2021, 1, 1), #date(2021, 1, 1)},
{ #date(2021, 1, 1), #date(2021, 1, 2)},
{ #date(2021, 1, 1), #date(2021, 1, 31)},
{ #date(2021, 1, 1), #date(2021, 2, 22)}
} )
in
Source
.
You can choose one of following M code examples and paste it in the Custom Column dialog box.
This performs a simple count of days.
Number.From([EndDate] - [StartDate]) + 1,
How does it work ?
The date has an interger equivalent, subtracting the StartDate from the EndDate and adding 1 back gives you the number of days between two dates including the end date.
.
What if you wanted to exclude days in the weekend? Note that the first parameter in List.Dates requires a date type, if you have a datetime value you can convert it using Date.From([StartDate])
List.Count(
List.Select(
List.Dates( [StartDate], Number.From([EndDate] - [StartDate]) + 1, Duration.From(1)),
each Date.DayOfWeek(_, 1) < 5
)
)
How does it work (from the inside out)?
- create a list of dates from the StartDate until the EndDate
- select only those dates, where the DayOfWeek no. <5 (with Day.Monday as first day)
- finally count all remaining elements in the list
.
And what if you also want to exclude holidays? Note. A query called Holiday Table that contains a Date Column column with holiday dates is required!
List.Count(
List.Select(
List.Difference(
List.Dates([StartDate], Number.From([EndDate] - [StartDate]) + 1, Duration.From(1)),
List.Transform(#"Holiday Table"[Date Column], Date.From)
), each Date.DayOfWeek(_, 1) < 5
)
)
How does it work (from the inside out)?
- create a list of dates from the StartDate until the EndDate
- remove all dates that are present in #“Holiday Table”[Date Column]
- select only those dates, where the DayOfWeek no. <5 (with Day.Monday as first day)
- finally count all remaining elements in the list
.
Don’t want to Count days but create a Record for each day instead?
- Just omit the List.Count function, you’ll get a list object
- Use the sideward arrows to “Expand to New Rows”
.
@BrianJ
Has posted about Imke Feldmann’s custom function for NETWORKDAYS, here:
And created a video demonstrating how that works (it also covers a DAX solution).
.
I hope you’ll find this useful,
If you have any questions, just reach out and let me know! 
To let me know you’ve appreciated this post give it a 