# Calculate the difference in days between two dates in Power Query

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)?

1. create a list of dates from the StartDate until the EndDate
2. select only those dates, where the DayOfWeek no. <5 (with Day.Monday as first day)
3. 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)?

1. create a list of dates from the StartDate until the EndDate
2. remove all dates that are present in #“Holiday Table”[Date Column]
3. select only those dates, where the DayOfWeek no. <5 (with Day.Monday as first day)
4. 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

6 Likes