Skip to main content

Posts

Recent posts

How To Use Expected Finish Date Feature In Primavera P6 || How To Apply Deadlines To Your Project

In this tutorial, let us learn How to use the Expected Finish Date feature in Primavera P6 or How To Apply Deadlines To Your Project This is the sample schedule. If you go to the Status tab in the activity details, you can see the expected finish field below the Finish date. So what is this expected finish date and how do we use it? Now, go to the scheduling option and find that the "Use Expected Finish Dates" option is switched on to enable the Expected finish date act as a constraint. You should be using the expected finish constraint when you do not know the duration but sure about the finish date. so, when this constraint is applied, every time you schedule the project, the P6 will calculate the new remaining duration based on the expected finish date constraint. For example, in this sample schedule. For Activity C, let us set the Expected finish date as 30th Apr'19. Now, while updating the project, you will update the finish date of Activity A by 6 days del…

How To Transpose Or Convert A Single Column To Multiple Columns In Excel || dptutorials

Hello Friends, Welcome to dptutorials.

In this tutorial, I will show you How To Transpose Or Convert A Single Column To Multiple Columns In Excel

There can be multiple ways to perform this trick,

I will show you some of these in this tutorial now.

So, let us go to the 1st method:

1. Using Indirect Function First consider this as an example, where I have got entries in a single column like this and let us try to convert these into 3 columns.

Enter the formula in Cell C1 as =INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1)) Copy and paste it 2 cells to the right, then down until you start to get zeros (delete these zeroes later). In this formula, A1 points to the first item in the column you want to re-arrange.

To change the number of columns, modify 3 in the formula into something else. Got it right.

This is one way to do this trick.

2. Now let us see the 2nd way of doing this: Using OFFSET Function Enter the formula in Cell C1 as =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*3,0)

Copy a…

Primavera P6 Export To Excel And Color WBS Levels Automatically || How To Color WBS Levels

In this tutorial, let us learn how to Color WBS levels after Primavera P6 Export To Excel.

After exporting activity table from Primavera P6 to Excel, it’s very hard to tell which activity belong to which WBS.

So in this tutorial, let us see how to color WBS levels automatically so we can have a report like in Primavera P6.

This is my sample schedule in primavera,

1. I press Ctrl+A to select entire data and copy the data and then go to Excel sheet and paste it.

2. Add a column left to Activity Id i.e column A and name it as WBS level to show the WBS level for each activity.

3. enter the formula in Cell A2 as = =((FIND(TRIM(B2),B2))-1)/2

4. Hit enter and then apply the same formula to all the cells in column A to see WBS level for each activity.

5. See now, you can view the WBS level for each activity this way easily in Excel after exporting the Primavera P6 Schedule.

And Now let us color the WBS levels.

1. Select the entire Data range.

2. Go to Conditional Formatting and click on New …

Automatically Add Date/Time to a Cell When You Enter || Record Date and Time when Value is Changed

We know that by using the functions Today() and Now() in Excel, we get the current Date and Time but these values will not be static and keep on updating on saving your workbook.

But if we need to get a static date into a cell after data is entered into other cells.

Here is the solution, watch this video to know this trick.

Let’s suppose when data is entered in column A, we want to have the corresponding current dates into column B in the same row.

Follow these steps now to perform

Step1: Right click on the sheet tab, select view code option

Step2: Copy the code ( I have shared this code in the description as well) Code @ http://bit.ly/2Is1EbD And paste into this VBA window.

Step3: Exit this VBA window and go back to the Excel workbook

Step4: Now, let us test it by adding any data to any cell in the range A1:A1000.

You will see the current date appearing in the corresponding cell in column B.

It is awesome right!!!

If you want to have both Date and time in place of the current date.

How To Identify WBS Level For Activity After Primavera P6 Export To Excel

In this tutorial, let us learn how to identify WBS level for activity after Primavera P6 Export To Excel.

After exporting activity table from Primavera P6 to Excel, it’s very hard to tell which activity belongs to which WBS. So in this tutorial, let us see this with an example. This is my sample schedule in primavera,

1. I press Ctrl+A to select entire data and copy the data and then go to Excel sheet and paste it.

2. Add a column left to Activity Id i.e column A and name it as WBS level to show the WBS level for each activity.

3. enter the formula in Cell A2 as =((FIND(TRIM(B2),B2))-1)/2

4. Hit enter and then apply the same formula to all the cells in column A to see WBS level for each activity.

5. See now, you can view the WBS level for each activity this way easily in Excel after exporting the Primavera P6 Schedule.

So, friends, this is all about this trick

If you have enjoyed this tutorial, please give me a like, share and comment.

For further more interesting videos, please do s…

How To Extract A Excel Workbook's Name and Path || How to retrieve the workbook’s name, filepath

Hello Friends, Welcome to dptutorials

If you want a cell to return the name of a workbook, or the workbook's filename and path.
Then this is the tutorial to watch out.

In this tutorial, let us use a small macro to retrieve the workbook’s name, file path, and sheet name.

We can do the same trick using the formulae.

Right click on the sheet tab and click on view code, or press Alt+F11 to go to VBA window.

Select insert, module

Copy this code and paste into this module,

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function Function Filepath() As String Filepath = ThisWorkbook.FullName End Function Function SheetName(rAnyCell) Application.Volatile SheetName = rAnyCell.Parent.Name End Function

Close this VBA window and go back to the Excel window.

Go to Formulas tab, click on insert function, under the category, and select the user-defined, you will find three functions Filepath, sheet name, and workbook name.

If you click on file path and click ok, you get …

How To Merge Two Separate Schedules Into One Master Schedule In Primavera

In this tutorial, let us learn how to Merge two separate schedules into One Master schedule in Primavera P6.

In this example, I am considering two separate projects which are these two, One is master schedule and one is the subproject.

First, let us open the subproject and export this schedule as a .XER file onto Desktop. and now let us start the exact process of merging two separate schedules.

1. As a first step, We need to open the project schedule into which we want to insert the other project as a sub-project. So, let us open the Master schedule here.

2. Go to Import, select XER format, Click Next

3. Select Project and click Next

4. Now select the Subproject .xer file from the Desktop and click Next

5. Under Import action, select the option as “Update Existing Project” and under import to section, select the Master schedule which is already open.
Click Next and Finally Click on Finish.

6. Please note and make sure that your WBS and activity IDs should not conflict with each of th…

How To Name a Workbook With the Text in a Cell || Save the Workbook with the cell Name using VBA

Hello Friends, Welcome to dptutorials.

If you want to save your excel workbook using the text in a specific cell as a filename, you need to watch this tutorial.

Click on the cell B2, enter some text and save it, you can see the file name as same as cell B2.


Let us learn this trick now, how to Name a Workbook with the Text in a Cell

Step1: Right click on sheet1 tab and select the view code option or else press Alt+F11 as a shortcut to go to VBA window.

Step2: Insert module and copy this code

Sub SaveAsCell( ) Dim strName As String On Error GoTo InvalidName strName = Sheet1.Range("B2") ActiveWorkbook.SaveAs strName Exit Sub InvalidName: MsgBox "The text: " & strName & _ " is not a valid file name.", vbCritical, "dptutorials.com" End Sub


Step3: And paste it here in the VBA window


Step4: Close this VBA window and go back to the Excel sheet.


Step5: From the menu bar, go to view tab and under macros, click on view macros

Step6: Select the Sav…

How To Show Only Some WBS In The Gantt Chart In Primavera P6 || Free Primavera p6 Online tutorials

In this tutorial, let us learn how to show only some WBS in the Gantt Chart in Primavera P6

Generally, Gantt chart will show bars for each activity and

If your Schedule is too big, sometimes you may feel it is distracting a lot. and you may sometimes like to present a report to show and focus only some of the WBS, rather than distracting with all bars.

So let us see how we can do this in Primavera P6.

This is my sample schedule with around approximate 1500 activities. and you can see the cluttering in the Gantt chart with all relationships

So, to do the trick, right click on the Gantt chart portion, click on Bars,  you select the bar which is being shown in the Gantt chart. Go to bar settings, by default the "show bar when the collapsed" option is checked. Now you need to uncheck them all.

Then you can see clean Gantt charts like this whenever you collapse and expand any particular WBS element in Primavera P6.

So, friends, this is all about this trick for now.

If you have e…