Microsoft Project American Dates

[Alastair1990]

Hello,

I am trying to export data from Project 2010 into Excel 2010 so that I can generate a chart of Planned tasks Vs. Time and Actual Start Tasks Vs. Time. I can therefore visually see where the project has moved away from the plan.

I am exporting:
Project Name
Duration
Start Date
Finish Date
Actual Start
Actual Finish

The actual start data and actual finish data are exported in the American style date format: MM/DD/YYY HH/MM/SS.
I have looked at the regional settings and Project states English UK.

Is there another method to ensure that the dates are in UK format when I export them?

I haven't found a way using VBA, equations or cell format to rectify the American date issues and therefore wanted to rectify the issues at source.

On another note, do I need to do this task manually in the first place? Is there a way that I can produce charts showing the aforementioned detail without the need to export into excel? It must be in real-time, ie, make reference to project completion to today's date.

Thanks for your help.

 

[JulieInMaine]

Hi Alastair1990,

The data you are attempting to export can be displayed visually in Project by adding the Baseline bars to a Gantt chart view. Exporting to Excel would need constant re-export. Project also calculates variance automatically based upon baseline data - so you can easily see Start Variance, Finish Variance, Work Variance, Cost Variance and Duration Variance.

I'd create a couple of custom tables gathering the data you need and perhaps create a custom view with nicely formatted Gantt bars.

I hope this helps.
Julie

 

[SkipVought]

hi,

A DATE is a DATE! The value that actually gets exported from Project is a NUMBER like right now, in North Texas the Date/Time is 41463.63604, which can FORMAT as 7/8/2013 15:15, for instance or with a quick NUMBER FORMAT change, 8/7/2013 3:15 PM

What you want is a different NUMBER FORMAT, which you can very simply do in Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JulieInMaine]

Actually Skip due to reasons I cannot explain, Project does odd things with exported data. The date/time cannot be directly reformatting in Excel - the UK date format won't stick. So, the trick is to use the DateValue function in Excel and then re-format the dates in English UK.

See:

http://answers.microsoft.com/en-us/...i-cannot/f48a4909-b130-4890-817b-f2ac5367bba5

 

[SkipVought]

Assuming that the Excel 'dates' are in MDY structure, but the NUMBER FORMAT is GENERAL, it means that the value is just TEXT and not a NUMBER.

Here's the KwikFix.
[tt]
Enter a number 1 in an empty cell anywhere in Excel

COPY that cell

SELECT the cells containing the fake dates

Right-Click in the selection and select Paste Special

In the Paste Special Window select the MULTIPLY Option Button and hit OK

Right-Click in the selection and select Format Cells and assign the Custom Format
[/tt]
The TEXT values are coerced by multiplication by 1, into a conversion to DATE/TIME Numbers that you can format any why you like.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You could also
[tt]
SELECT the column containing the fake dates.

Data > Text to columns -- FIXED WIDTH

NEXT

UNCHECK all selection -- NEXT

select MDY

FINISH
[/tt]
The fake dates become real DATE/TIME values. Use Custom Number Format as desired.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I almost NEVER export data as a worksheet. Excel can and will CHANGE data, even other than your experience, in the Open event.

Rather I export a .csv file and IMPORT the data into Excel via Data > Get external data > TEXT FILES, where I can control the data types of each column if data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Alastair1990]

@Skipvought

This worked a treat:

"You could also

SELECT the column containing the fake dates.

Data > Text to columns -- FIXED WIDTH

NEXT

UNCHECK all selection -- NEXT

select MDY

FINISH

The fake dates become real DATE/TIME values. Use Custom Number Format as desired"

I had to start a fresh for it to work, but it is now working beautifully.

Thanks for the fast responses!