PowerPivot Date

[gavs73]

Hi

I'm trying to enter a calculated Date field in PowerPivot but I am getting an error message, I'm not sure why.

I can get a breakdown of the date components...

=LEFT([DOC_ITEM_TERM_DATE_KEY],4)&mid([DOC_ITEM_TERM_DATE_KEY],5,2)&RIGHT([DOC_ITEM_TERM_DATE_KEY],2)

But as soon as I add DATE and replace the &s, I get an error...

=date(LEFT([DOC_ITEM_TERM_DATE_KEY],4),mid([DOC_ITEM_TERM_DATE_KEY],5,2),RIGHT([DOC_ITEM_TERM_DATE_KEY],2))

Thanks for any help.
Gav

 

[Ozeroth]

Hi there,

[DOC_ITEM_TERM_DATE_KEY] is yyyymmdd right?

Your DATE formula works for me in Excel 2010 & 2013.
But you could try this to ensure the text values are cast as numbers:

= DATE ( VALUE ( LEFT ( [DOC_ITEM_TERM_DATE_KEY], 4 ) ), VALUE ( MID ( [DOC_ITEM_TERM_DATE_KEY], 5, 2 ) ), VALUE ( RIGHT ( [DOC_ITEM_TERM_DATE_KEY], 2 ) ) )

 

[gavs73]

Thanks Ozeroth