Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

P3EC Export as XER file Calendar Data Issue

4 replies [Last post]
John Griffiths
User offline. Last seen 13 years 40 weeks ago. Offline
Joined: 11 Jan 2005
Posts: 5
Groups: None
Does anybody have any information that explains the method being used to store the exception information for non-working days that have been applied to a P3EC calendar when a project is saved in the XER format? The example below is part of the exceptions data in the calendar section of an xer file:
(0||Exceptions()
((0||0(d|36126)())
(0||1(d|36153)())
(0||2(d|36311)())

I am showing 3 of a total of 105 exceptions but have no idea what the 5 didgit number means.

Any help would be really appreciated. Thanks

Replies

Denise Eagle
User offline. Last seen 4 years 43 weeks ago. Offline
Joined: 14 Jul 2014
Posts: 5
Groups: None

Hi,

I have just figured it out too, it took me days. I am working in SQL and I needed to read the database, specifically the CALENDAR table and the clndr_data field. This is the same as the data that you have above, it is in XER format as well.

So when you see the numbers...

(0||Exceptions()
((0||0(d|36126)())
(0||1(d|36153)())
(0||2(d|36311)())

You will note that there are empty brackets after the numbers. If someone is working on that day then the brackets would contain the data for the hours to be worked in that day. So there is no work happening on the above days.

This is what I search for "d|36126)())", but used the date that I had converted to a number.

First I converted todays date to a number and then double checked it in excel. If you put a date into excel and then change the format of that cell from date to number it will give you the equivalent number. In SQL I cast the date to an integer and used that number, but, the number was out by 1, not sure, so I manually added one as an offset number to ensure I had the right number before searching.

Next, I created a function to do a string search but included the empty brackets. If it returned 0 then it was a normal working day, and if it returned a value than then the resource was not working on that day.

Hope that helps,

Denise

Denise Eagle
User offline. Last seen 4 years 43 weeks ago. Offline
Joined: 14 Jul 2014
Posts: 5
Groups: None

I am not sure that is correct. I have a date number of 36982 that is supposed to be a date in July 2014 but with the cdate function above comes out as 1st April, 2001, so I am not quite there with converting this number to a date.

When I put this number into Excel and convert the value to a date, it too comes out as 1st April, 2001.

When I open this particular calendar in P6 then I can see that the calendar is July 2014.

I am trying to interpret the codes and not having too much luck. :-(

John Griffiths
User offline. Last seen 13 years 40 weeks ago. Offline
Joined: 11 Jan 2005
Posts: 5
Groups: None
Hi Ronald,

Thanks for your help I realised on Saturday after having checked all the calendars that were in the XER file in P3 that they all started with the same number and loaded the exception numbers into Excel and changed to dates and bingo. Yes agree with you why start the dates from 1899 is soemthing of a mystery.

Once again thanks for your assistance.

John
Ronald Winter
User offline. Last seen 5 years 4 weeks ago. Offline
Joined: 4 Jan 2003
Posts: 928
Groups: None
Those 5-digit numbers are day numbers, beginning with 12/30/1899. I am not sure why Microsoft starts there but if you perform the VBasic function

Print cdate(36216)

you get the answer,

11/27/1998

Good luck!