Forum Discussion

TIGER8855's avatar
TIGER8855
Helper I
3 years ago
Solved

exclude text values when using DATEDIFF

I'm using DATEDIFF to find the number of days between today and dates in column however the column also has some values that are text therefore the the calculation doesn't work.  Can anyone advise...
Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey TIGER8855

    you can do this with two steps in Power Query, create a new column with this code
    try Date.FromText ([maybe a Date] )
    otherwise null


    Then change the data type to Date
    This result might look like this:

    Hopefully this provides what you are looking for.

    Regards,
    Tom

TomMartens's avatar
TomMartens
Super User
3 years ago

Hey TIGER8855

you can do this with two steps in Power Query, create a new column with this code
try Date.FromText ([maybe a Date] )
otherwise null


Then change the data type to Date
This result might look like this:

Hopefully this provides what you are looking for.

Regards,
Tom

TIGER8855's avatar
TIGER8855
Helper I
3 years ago

Thanks TomMartens. That works. My original DATEDIFF can now be used on this new column.