PowerPivot: Date calculation from text field

[farrusete]

Hi,

Im starting with PowerPivot and just moving previous reports to practice. I have a date column which includes both dates (dd/mm/yyyy hh:mm:ss format or just "unlimited" text).

I created a calculated field with

=IFERROR(DATE(MID([end date], 7,4),MID([end date], 4,2),LEFT([end date], 2))+TIME(MID([end date], 12,2),MID([end date], 15,2),MID([end date], 18,2)), DATE(1,1,1))

to get this column and make further calculations (by ignoring values with "01/01/1901" returned by DATE(1,1,1)) but I was wondering if there could be a better approach to get that field filled with null value or something like that

Thank you in advance,

 

[Somendra Misra]

Hi,

See the attached file. Normal Pivot table. Do look at calculated field formula and Number Format applied to it.

Correct me if this is not what you want.

Regards,