Latest Enterprise DNA Initiatives

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! :wink:

To let me know you’ve appreciated this post give it a :heart:

4 Likes

Adding some search terms and marking this thread as Solved.

Networkdays, Datediff, date difference, process duration