none
Save as Excel Binary data formatting RRS feed

  • Question

  • Hello, can anyone tell me how do I set the date format of tasks I export from Project 2010 and 2016 in to Excel when I save the Project file as an  Excel Binary Workbook format (*.xlsb)

    I have 2 users who save a MS Project file as Excel Binary they get different results, for both users the mapping wizards shows a preview of the Dates being the Format typically using in MS Project e.g. Mon 03/07/17 this is the format I'd like to keep

    When I open the Excel Binary file in Excel 2010, the format does not remain the same as the Project date format and changes to  the below in Excel.

    How do I control this please? I have tried this on my own computer with Project 2010 and Project 2016

    The data format out of Project 2010 changes from Mon 03/07/2017 to 03/07/2017 08:15 when opened in Excel 2010

    The date format out of Project 2016 changes from Mon 03/07/2017 to 03 July 2017 08:15 when opened in Excel 2010

    I would like to keep the format the same as that in MS Project so Excel displays the data as Mon 03/07/17, but I don't want to have to code a macro that formats the date, once opened, I just want it to open in the same format as shown in the Excel Binary wizard preview (first image above).

    The actual data in Excel comes in as TEXT, so when you update the cell (F2, Enter) Excel reassesses the cell content as turns it in to a date, and I can format the cell to ddd dd/mm/yy but again I don't want to, I want it to open as per the Excel binary wizard preview.

    Thank you in advance


    Wednesday, February 28, 2018 3:20 PM

All replies

  • David,

    As far as I know you can't. Remember that Project stores data in it's "full" form regardless of how it is displayed in a view. For a date, that means it stores the time (including hours, minutes and seconds and AM or PM) along with the month, day and year. So when the data is exported that full form is exported and that's what you see in Excel.

    As you already know there are various ways to re-format the data in Excel to get the desired format but that process requires manual or automated (e.g. macro) action. This is one reason I never use an export/import map and instead use VBA for transfer of data between Project and Excel. Once it's set up, it's just as easy to click a ribbon button for the macro as it is to create or select an export map to effect the transfer.

    John

    Wednesday, February 28, 2018 3:57 PM