Duration element in XML

Question

Hi,

I am about to write an XML file (with a VBA, the table for the data is stored in Excel) which is compatible to MS Project.

Everything works with uploading the XML file (Start and end date are uploaded) except that the duration is not displayed.

As I read (http://msdn.microsoft.com/en-us/library/office/bb968640(v=office.12).aspx), the duration element is not required, just optional.

I expected that the duration is automatically calculated when start and end date is uploaded, but it is not.

Does anybody know how I can fix that. Is there a function in MS Project that (re)calculated the duration by a click or is there

a macro available for that?

Since the duration is a TimeSpanValue, it is not clearly written in days in XML, I can not calculate it by myself within the XML file.

When I exported a file from MS Project to XML, I saw something like PT8H0M0S as the TimeSpanValue.

<Duration>   TimeSpanValue </Duration>

Kind regards,

Nathalie

Answer 1

Nathalie,

When I open your XML file in Project the task is in manual mode and the calculation option is set to manual. The Duration field shows 0 days, which it should under those conditions, but at least there is data in the duration field. If the task is set for auto-scheduled then the XML import shows a duration of 1 day regardless of whether calculation is set for auto or manual. But in either case there is always data in the Duration field, it is never "blank". So to answer your question, no, Project is working as expected with respect to the duration. This is a screen shot of the import directly from your XML file.

Something else I noticed is what appears to be a disconnect in the calendar. Based on our previous discussion I inferred that you not only have a 6 hour work day starting at 9:00 am and ending at 4:00 pm, but the work week also goes from Sunday through Thursday. However I see your custom calendar does have the noted work times but is for a standard Monday through Friday work week. Did I miss something?

It's been several years since I worked with an import of data from Access to Project and it was with an earlier version of Project. What I remember is that I used a macro to open the Access database using ODBC. From there the code read in each recordset and manipulated it as necessary to produce the desired Project file. In other words, it was a direct Access to Project import with no intermediate file format (i.e. no  XML, no Excel, no anything).

John

Answer 2

Hi John,

you interpreted it the right way.

I can show you a scenario: Open the following XML code as a file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Project xmlns="http://schemas.microsoft.com/project">

<SaveVersion>14</SaveVersion>

<Name>testupload999.xml</Name>

<Company>XY</Company>

<Author>New</Author>

<CreationDate>2013-07-25T09:00:00</CreationDate>

<LastSaved>2013-08-01T14:00:00</LastSaved>

<ScheduleFromStart>1</ScheduleFromStart>

<StartDate>2013-07-25T09:00:00</StartDate>

<FinishDate>2014-10-29T16:00:00</FinishDate>

<FYStartDate>1</FYStartDate>

<CriticalSlackLimit>0</CriticalSlackLimit>

<CurrencyDigits>2</CurrencyDigits>

<CurrencySymbol>$</CurrencySymbol>

<CurrencyCode>USD</CurrencyCode>

<CurrencySymbolPosition>0</CurrencySymbolPosition>

<CalendarUID>3</CalendarUID>

<DefaultStartTime>09:00:00</DefaultStartTime>

<DefaultFinishTime>16:00:00</DefaultFinishTime>

<MinutesPerDay>360</MinutesPerDay>

<MinutesPerWeek>1800</MinutesPerWeek>

<DaysPerMonth>20</DaysPerMonth>

<DefaultTaskType>0</DefaultTaskType>

<DefaultFixedCostAccrual>3</DefaultFixedCostAccrual>

<DefaultStandardRate>0</DefaultStandardRate>

<DefaultOvertimeRate>0</DefaultOvertimeRate>

<DurationFormat>7</DurationFormat>

<WorkFormat>2</WorkFormat>

<EditableActualCosts>0</EditableActualCosts>

<HonorConstraints>0</HonorConstraints>

<InsertedProjectsLikeSummary>1</InsertedProjectsLikeSummary>

<MultipleCriticalPaths>0</MultipleCriticalPaths>

<NewTasksEffortDriven>0</NewTasksEffortDriven>

<NewTasksEstimated>1</NewTasksEstimated>

<SplitsInProgressTasks>1</SplitsInProgressTasks>

<SpreadActualCost>0</SpreadActualCost>

<SpreadPercentComplete>0</SpreadPercentComplete>

<TaskUpdatesResource>1</TaskUpdatesResource>

<FiscalYearStart>0</FiscalYearStart>

<WeekStartDay>0</WeekStartDay>

<MoveCompletedEndsBack>0</MoveCompletedEndsBack>

<MoveRemainingStartsBack>0</MoveRemainingStartsBack>

<MoveRemainingStartsForward>0</MoveRemainingStartsForward>

<MoveCompletedEndsForward>0</MoveCompletedEndsForward>

<BaselineForEarnedValue>0</BaselineForEarnedValue>

<AutoAddNewResourcesAndTasks>1</AutoAddNewResourcesAndTasks>

<CurrentDate>2013-08-01T08:00:00</CurrentDate>

<MicrosoftProjectServerURL>1</MicrosoftProjectServerURL>

<Autolink>0</Autolink>

<NewTaskStartDate>0</NewTaskStartDate>

<NewTasksAreManual>1</NewTasksAreManual>

<DefaultTaskEVMethod>0</DefaultTaskEVMethod>

<ProjectExternallyEdited>0</ProjectExternallyEdited>

<ExtendedCreationDate>1984-01-01T00:00:00</ExtendedCreationDate>

<ActualsInSync>1</ActualsInSync>

<RemoveFileProperties>0</RemoveFileProperties>

<AdminProject>0</AdminProject>

<UpdateManuallyScheduledTasksWhenEditingLinks>1</UpdateManuallyScheduledTasksWhenEditingLinks>

<KeepTaskOnNearestWorkingTimeWhenMadeAutoScheduled>0</KeepTaskOnNearestWorkingTimeWhenMadeAutoScheduled>

<WBSMasks/>

<Calendars>

<Calendar>

<UID>1</UID>

<Name>Standard</Name>

<IsBaseCalendar>1</IsBaseCalendar>

<IsBaselineCalendar>0</IsBaselineCalendar>

<BaseCalendarUID>-1</BaseCalendarUID>

<WeekDays>

<WeekDay>

<DayType>1</DayType>

<DayWorking>0</DayWorking>

</WeekDay>

<WeekDay>

<DayType>2</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>08:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>17:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>3</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>08:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>17:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>4</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>08:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>17:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>5</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>08:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>17:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>6</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>08:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>17:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>7</DayType>

<DayWorking>0</DayWorking>

</WeekDay>

</WeekDays>

</Calendar>

<Calendar>

<UID>3</UID>

<Name>CustomCalendar(6h)</Name>

<IsBaseCalendar>1</IsBaseCalendar>

<IsBaselineCalendar>0</IsBaselineCalendar>

<BaseCalendarUID>-1</BaseCalendarUID>

<WeekDays>

<WeekDay>

<DayType>1</DayType>

<DayWorking>0</DayWorking>

</WeekDay>

<WeekDay>

<DayType>2</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>09:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>16:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>3</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>09:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>16:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>4</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>09:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>16:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>5</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>09:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>16:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>6</DayType>

<DayWorking>1</DayWorking>

<WorkingTimes>

<WorkingTime>

<FromTime>09:00:00</FromTime>

<ToTime>12:00:00</ToTime>

</WorkingTime>

<WorkingTime>

<FromTime>13:00:00</FromTime>

<ToTime>16:00:00</ToTime>

</WorkingTime>

</WorkingTimes>

</WeekDay>

<WeekDay>

<DayType>7</DayType>

<DayWorking>0</DayWorking>

</WeekDay>

</WeekDays>

</Calendar>

</Calendars>

<Tasks>

<Task>

<UID>1</UID>

<ID>1</ID>

<Name>Task1</Name>

<Active>1</Active>

<Manual>1</Manual>

<Type>0</Type>

<IsNull>0</IsNull>

<OutlineLevel>1</OutlineLevel>

<Start>2013-04-06T09:00:00</Start>

<Finish>2013-06-30T16:00:00</Finish>

<ManualStart>2013-04-06T09:00:00</ManualStart>

<ManualFinish>2013-06-30T16:00:00</ManualFinish>

<DurationFormat>21</DurationFormat>

<Summary>0</Summary>

<EarlyStart>2013-04-06T09:00:00</EarlyStart>

<EarlyFinish>2013-06-30T16:00:00</EarlyFinish>

<LateStart>2013-04-06T09:00:00</LateStart>

<LateFinish>2013-06-30T16:00:00</LateFinish>

<CalendarUID>3</CalendarUID>

<IgnoreResourceCalendar>0</IgnoreResourceCalendar>

</Task>

</Tasks>

<Resources>

</Resources>

<Assignments>

</Assignments>

</Project>

I need to calculate in Excel since the Access DB I have is not really compatible to MSProject by default.

So I have to play with some tables to make it compatible. The projects/task and resources are not the problem, but the assignments are (assignments are entered i percentages, not so in Access; I have monthly tasks in Access and in MS Project you can create tasks with different durations and so on) . I am 100% sure that I have to do that in Excel since it is not compatible with MS Project otherwise. So it is complex in someway.

Can you see why the XML code is not working with the duration? Perhaps the timephased data is a problem? Do I have to add something that I missed?

I used some random now with different durations (1 day, 5 days, 10 days, 55 days) and exported them to XML. For me it seems to me that the PT section of the duration is the days multiplied with 8 (for hours?) since for example PT440H0M0S means a duration of 55 days. Could that be?

Kind regards,

Nathalie

Answer 3

Nathalie,

We must be talking about different things. This is how I interpret what you are saying. You have an XML file. You open that file in Project. The Start and Finish fields have data but the Duration field does not. I don't understand because there is no scenario I can create that will give no value whatsoever in the Duration field in Project - manually scheduled, auto-scheduled, calculation set to manual, or calculation set to auto. Are you absolutely sure you are talking about he normal Duration field?

It's still not clear why you are going through several different operations and formats (i.e. XML to Project to XML to Excel to XML to Project), to get to your end goal Project plan. I assume you are still importing data in an Access database to Project as the first step. Once you get that data into Project, there is no reason, that I can see, for exporting the data to Excel to do some calculations and then send it back to Project. Whatever calculations you need for Project can always be done in a Project based VBA macro - no other application or format is needed. Now, if you need to export Project data to another application for custom formatting and reporting, then that's a different story. Either I'm missing something or it appears you are taking something that would be very simple and straightforward to accomplish and making it very complex.

As usual, I'm quite confused.

John

Answer 4

Hi John,

I meant the first case. I load the XML file with start and finish date. Both are displayed, but no duration. This field is blank. If I just choose this date manually again, then yes there is the duration I need. But I can not do that for thousands of tasks. I think this scenario is because of XML uploading and not manual typing in the dates.

Yes there is. I have to upload them, "do something" in MS Project (can only be and export them again them again to Excel. After that, I have to calculate some things in Excel since it is not possible MS Project (I need more tables which MS Project does not provide) and final upload it to MS Project again.

Can you show an example how you can upload a task directly from Excel to MS Project?

I have seen that a direct import of Excel to MS Project does not provide the option of importing outline codes as well. That is main reason for choosing XML, because the structure is very easy and provides everything I need, except the duration at the moment.

Nathalie

Answer 5

Nathalie,

Are you saying that when you opened an XML file in Project, the Start and FInish fields are populated but the Duration field was not? If so, I know of no scenario in Project wherein the Duration field will be blank (given a start and finish).

If instead you are saying that the Duration field has a value but it does not accurately represent the difference in working time between the start and finish, then it is because Project's calculation option, (File/Options/Schedule group), is set to "off". Turn that on and the Duration field will be automatically calculated. Note: this is a Project setting (i.e. Global), not a file setting.

With regard to your TimsSpanValue, I don't know what the "PT" means, but the "8H0M0S" means 8 hours, 0 minutes and 0 seconds.

Just curious. You say the data is stored in Excel. Is there a specific reason you are going through an XML to Project and not simply exporting Excel data directly to Project via a macro?

John