Date conversion

[paivers]

I have a text field that contains a combination of dates and nulls (i.e. ISBLANK returns false, but ="" returns true). The problem is I want to convert to Datetime to do arithmatic on it, but when I try DateValue([MyDate]) I get a type mismatch even though that takes a text. I though it was the content perhaps so I tried
=if([MyDate]="",Date(1900,01,01),DateValue([MyDate]))

But still a type mismatch. The source of the field is an MDX query to a traditional cube, but that shouldn't make a difference. How can I convert this to a date?

 

[ChrisR]

Hi,

Can you post up the format that your text date is in?

What error are you getting? Is it #Value ?

Regards,
Chris

 

[ChrisR]

Just to add to the above, If I have :-

21 Jan 2012 formatted as text then =DATEVALUE on this Text date returns the date serial number (Which you can use for calculations)

However, if I use:-

Jan 21 2012

Then I get a #Value error...........

Is this what you are seeing?

 

[ChrisR]

Edit..............

Just noticed this is in the powerpivot section, so not sure if my comments above are still applicable (I picked up your question from the zero replies section!) Apologies if they are not relevant........

 

[Jacob Barnett]

Powerpivot will not allow multiple data types in the same formula (unlike Excel) and it sees "" as text.

try blank() instead of ""

 

[theBardd]

having you tried changing the data ype of the date column to text in PowerPivot?

 

[paivers]

Sorry for the delayed response, I'll try to address all the suggestion. The specific error is:
>> Warning 1 Calculated column 'MyTable'[MyDate] : The following system error occurred: Type mismatch.

In its simplest form the expection I have is:CalculatedColumn=datevalue([MyDate])
where the CalculatedColumn is type Date

MyDate is of type "Text" and contains values including blanks and values such as "2013-01-14 14:45:00.000". Is is sourced from an MDX query from SSAS.

So all I want to do is get the date text to date format. My basic goal is to tell whether I have a date within the past year. Blank would be negative, but if I have a date I need to get to date format so I can calculate, but no luck doing that.