@padarthi,
Thanks for providing us template file and details.
Well, this is MS Excel’s behavior and nothing to do with Aspose.Cells APIs. MS Excel shows the complete Date value instead of formatted value from the source cells when you double-click on data section of the PivotTable, it will always show the details with complete DateTime value for the underlying data item(s) in the Table/List Object. I tried to to accomplish the task in MS Excel manually but to no avail. If you could accomplish the task (automatically change the DateTime value (as per the formatted cells of the source cells) when it is opened into a new sheet Table/List Object) in MS Excel manually, kindly do provide a sample file with complete details and screenshots on how to do it, we will check on how to do it via Aspose.Cells APIs.