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 –

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

  1. 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!

  2. 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

  3. 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

  4. 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.

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s