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 –
Erik Svensen - Blog about Power BI, Power Apps, Power Query 
Will this work if the column is a datetime field?
If both field is datetime – yes it should work – other wise you can subtract time in the same menu
How about if I only need “business” days? Any shortcuts?
With business days – do you mean mon-fri or do you wish to take other holidays into account?
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!
Hi – There is no datediff function in Power Query 🙂
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?
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
Hi Erik,
How do I do this from a single date field and today as the classic TODAY() doesn’t seem to work.
Thanks,
In Power Query ?
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,
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
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.
Hi Tapendar – could you provide some sample data and I will give it a shot
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.
Hi,
Based on the error message one of your columns has the DateTime data type – and that has to be converted to a Date before you can subtract the columns
/Erik
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 ?
Duration.Days([ToDate]-[FromDate]) should do the trick