Posted on in Power Query

Quick #PowerQuery trick – Get duration days between two dates

Just a quick tip that you might not be aware of in the Power Query Editor.

If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days

This will give you the number of days between the dates in the selected columns

Use the formula bar to rename the new column by modifying the step

 

Hope this can help you too –

23 thoughts on “Quick #PowerQuery trick – Get duration days between two dates”

  3. Hi – interesting – is there a reason you wouldn’t use datediff function? I’m asking as when i use datediff against redshift database it gives me some rows correct and others a big negative number. If i use your method it does seem to correctly calculate. i wonder if there’s an issue with datediff. thanks!

  4. Hi Erik, I have the same Question as Patrick. What i’d only like to see the Workdays (No holidays, no weekenddays) between the two columns?

    1. Hi Steve

      Do you have a dates table that has columns containing weekday number and an indication of the date is a holiday ?

      If you have then you can add a calculated column that contains a formula something like this Table.Rowcount(table.selectrows(datetable, each
      [date] >= [StartDate] and [date] <= [enddate] and [weekday] <=5 and [Holiday] = false))

      Answering via phone so no M intellisense 🙂

      Best
      Erik

  5. Hi Erik,
    How do I do this from a single date field and today as the classic TODAY() doesn’t seem to work.

    Thanks,

      1. Yes please. Tried Duration.Days([ActualShipDate] – TODAY()) and it errors
        also tried
        Duration.Days([ActualShipDate] – (Date.ToText(DateTime.Date(DateTime.LocalNow()),”dd/MM/yyyy”))) and although it lets me close custom column creation it then errors on my data and it doesn’t like the date format even though they are the same?

        Thanks,

      2. Hi James, You need to do it a little bit different as Duration.Days needs a Duration type value – so try

        Duration.Days(DateTime.FixedLocalNow()-DateTime.From( [ActualShipDate]))

        /Erik

  6. Hi Erik,
    Shift start date,state begin and state end columns are their in my data.state begin and state end columns representation is downtime of a machine.then how can i calculate runtime of a machine based on date selection and also each day consists of three shifts.

  7. Hi Erik, I’m struggling to find a way to subtract from a specific date. I would like to subtract from 01/01/2022 for all rows but I keep getting an error when I use the date: “Expression.Error: We cannot apply operator – to types DateTime and Date.”. Any ideas why it’s rejecting the date, I guess it’s the format? Thanks.

  8. i just want to calculate the number of weekdays between two days . ( No holiday’s , just removing week ends) any short cuts please in M Query ?

      1. Sorry not sure how to. It wont let me paste an image directly into this reply box. Everything looks exactly the same as your 1st screen shot above, except The subtract days option is missing. Mark

      3. Thanks for reply Erik, yes I have added it manually using Duration.TotalMinutes([EndTime]-[StartTime]). But having found your post I was puzzled as to why my BI desktop (Latest version) doesnt show the option you highlighted. Thought I was doing something wrong. Oh well, if there is no obvious answer, then I willsettle for the manual column. Thankyou for your time in responding, nice to know we have the support of people like yourself.

Leave a comment