Conversion Formulas / Cell Linking

Laura

I am cell-linking 2 sheets but 1 sheet records time in days and allocation:

 

Example:

2 days and 50% Allocation = total of 8 hours spent on the project over the course of 2 days.

(The duration can not be changed from days because this is our project management sheet and we use in the gantt chart to share with customers.)

 

 

The 2nd sheet is our cost sheet but we record time in hours and that can not be changed.

 

What is the simplest way I can possibly hide a column on the project management sheet that uses a formula (???) to convert the duration in days & allocation to hours, then cell link the hours result to the Cost Sheet?

 

 

Any other ideas or suggestions?

J. Craig Williams

Laura,

 

Note 1. You can put your allocation in hours, but it will still calculate as days

That is 8h = 1d for duration calculation, Gantt chart view, and the formula below.

 

To get your hours value:

 

a. create a column in the project sheet

b. add the following formula:

 

=(Duration23 * 8) * [Allocation %]23

 

for row 23.

 

Note 2: This will return 0 if either Duration or Allocation % is blank.

 

3. Copy that formula to the rest of the column.

 

4. Link that to your other sheet and then hide away.

 

Note 3: The eight (8) above assumes your work-day is set to 8 hours - that is configurable in the Project Settings. If you work-day is 10 hours, update as appropriate.

 

Hope this helps.

Craig