Forum Discussion
Issues with changing text format string to date format on data imported from msproject.
Good evening,
I work with Excel 2013. I have this text format "2017 March 29 17:00" to convert to a date format ( wihtout the hour) in Excel. This date comes from a microsoft project saved in Excel. I have tried both "datevalue" and "value", and it does not work. I have tried "text to column" and it does not work as well.
Any suggestions ? I was thinking that maybe there is a way of exporting to Excel from Msproject in a already compatible date format..
Thanks in advance.
Pierre
Hi,
In general yes, much better to find the way to export from MS Project the date recognizable by DATEVALUE() in Excel rather than parse the text string and combine back to recognizable Date format.
I'm not familiar with MS Project and have no own knowledge how to solve. Please check this discussion https://social.technet.microsoft.com/Forums/projectserver/en-US/c7453684-23a1-414c-8806-3ad80c3f5464/date-and-cost-formats-in-export-toward-excel-2013?forum=projectprofessional2010general. They suggest to export Scheduled Start field instead of Start Date. Perhaps Google will give more answers.
Hi pbosisio,
I suggest use following formula:
=LEFT(A1,SEARCH(" ",A1)-1) => 2017 (year)
=MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)-1) => March (month)
=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)-SEARCH(" ",A1,SEARCH(" ",A1)+1)-1) =>29 (day)
