Date formula returning "#INVALID DATA TYPE"

Shaun Dickens

Hi guys, I can't figure out what's happening here, hoping someone can point me in the right direction

Importing a table with a "Date" column set as primary column, Date format is YYYY-MM-DD

Trying to convert the value into a format I can use by splitting the date string into it's seperate components using MID function, and then recombining those values using the DATE function, but I'm getting an INVALID DATA TYPE error.

I tested the function in the 2nd Row inserting the numbers in D,M,Y instead of formulas, but that didn't work either (but the DATE function works fine if I put the numbers directly in there =DATE(2019,05,06), does the function just not support referenced cells?)

Thanks!

Paul Newcome

The MID function converts everything to text. Try wrapping each of those in a VALUE function.

=VALUE(MID(...................))

Paul Newcome

The MID function converts everything to text. Try wrapping each of those in a VALUE function.

=VALUE(MID(...................))

Shaun Dickens

That worked perfectly :) Thanks Paul! I'll remember that in future!

Paul Newcome

Happy to help! 👍️

The other option would be to nest the VALUE functions within the DATE function

=DATE(VALUE(Y1), VALUE(M1), VALUE(D1))

But I personally find it's easier to manage if you can wrap something in an additional function vs having to nest multiple new functions in the middle of one.