Dates Columns imported from Planner are not correct

Copper Contributor

Hi Guys,

 

I exported some Plans from Planner to Excel, all is fine except for the date columns, they are exported as texts. for example, please see below snap shot:

 

Salman_Alkweldy_0-1698046887349.png

 

The highlted cells are originally as follows:

2 August 2023

24 July 2023

2 October 2023

 

However, when I convert to the dates in excel, they will aprear as follows:

8 Feb 2023

24/07/2023 (remains text becayse there is no month numbered 24)

10 Feb 2023

 

even if I change the format of the cells to be DD/MM/YYYY, it will stick to the same wrong dates.

 

Thank you,

Salman

3 Replies

@Salman_Alkweldy You need to transform the text dates in column H to real dates with Text-to-columns on the Data ribbon. Skip through steps 1 and 2 (i.e. change nothing). In step 3 set the data format to 'Date' and indicate that the texts are in 'DMY' format and Finish.

 

Now, Excel will transform the texts to real dates and display them in the default date format based on your local system settings.

@Riny_van_Eekelen 

 

Solution described does work partially. "Created Date" column got converted in MMM-DD-YYYY format however rest two columns i.e. "Start Date" & "Due Date" didnot converted. infact excel file is closing abruptly. 

 

Created DateStart DateDue Date
03/08/202403/12/202403/15/2024
03/08/202403/08/202403/11/2024
03/07/2024 03/07/2024

 

@Ibrahim_Sayyed 

Text to Columns work on column by column basis. That means you shall repeat the procedure for each column separately.

Based on your screenshot on third step of the wizard it shall be selected Date->MDY.

 

This tool is in Excel for dozens of years. If you do all correctly it shall work.