Is it possible to get correct values from subtracting two date columns formatted as text?

[Anonymous]

Hey Everyone,

I currently have two date columns, however I had to change their format to "text", due to format/value error when I was trying to use the date columns in a IF and OR statement, with text values. However the results I get from trying to find the difference between these two columns is slightly off, not greatly off, but still off e.g. result of 1,283 versus what I should get at 1,252.

So, I'm not sure if its possible, but I really hope it is as it will my life much easier ...

Thank you in advance,

Regards,

P

[v-cherch-msft]

Hi @Anonymous

You may refer to below sample. The two date columns are formatted as text. If it is not your case, please share some data sample and expected output.

Regards,

Cherie

[Anonymous]

Hi CheRIE,

I've tried this and I am getting the error "Cannot convert value of type text to type date"

[v-cherch-msft · Accepted solution]

Hi @Anonymous

Try below measure. If it is not your case, please share your sample data for your scenario so that we could help further on it.

Column =
FORMAT ( VALUE ( Table5[Column2] ), "MM/DD/YYYY" )
    - FORMAT ( VALUE ( Table5[Column1] ), "MM/DD/YYYY" )

Regards,

Cherie

[Anonymous]

That worked perfectly, thank you

[Greg_Deckler]

Can you post some sample source data and perhaps some of the calculation formulas that you are using? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490