Custom column to find out in days/hours, how long it took between Created and Assigned on

[bryn987]

I have a column called Created and a column called Assigned On - Date/Time, formated as date/time and I want to find out the average duration between those.  If Assigned On - Date/Time is blank, then use today's date.

I have this, but Im getting an operator error:

Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
Operator=-
Left=5/1/2023 8:33:52 AM
Right=5/1/2023

Here is my code

if [#"Assigned On - Date/Time"] <> null then Duration.From([#"Assigned On - Date/Time"] - [Created]) else Duration.From(DateTime.LocalNow() - [Created])

Any ideas?

[bryn987]

Everything is formatted as date/time.

tackytechtom your code is just pulling back the assigned date/time value

[tackytechtom]

Hi @bryn987 ,

Thats a weird one. If i use your initial query, it actually works well.

do you get the error on all rows or just on a few single ones?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[bryn987]

I had a typo and once I fixed that, your code worked.

Thank you!

[Ahmedx]

you first need to convert the date to datetime

[tackytechtom · Accepted solution]

Hi @bryn987 ,

It seems Power Query has a problem with comparing the date/time column with the column formatted only as date.

Does it work with this one? 🙂

if [#"Assigned On - Date/Time"] <> null then Duration.From([#"Assigned On - Date/Time"] - DateTime.From([Created])) else Duration.From(DateTime.LocalNow() - DateTime.From([Created]))

Note, the DateTime.From function casts your date always as midnight, i.e. 1/5/2023 becomes 1/5/2023 00:00:00

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/