Excel working with Timescale Data?

[Joycelet]

Hi

I have exported data out from Project and have the following fields:

Resource Name, Status, Work Remaining, Start Date, End Date

What i need to be able to do is to show the work remaining by month, taking each month to have 21 working days available.

So for example if a task start 01/01/07 and takes 30 days then I would need to show 21 days in Jan and 9 in Feb, is this possible? Any help in the right directions appreciated.

Thanks

Nicola

 

[onedtent]

plz look at the workday function in xl. it's quite simple.

- onedtent Onedtent OnedTent

 

[Joycelet]

Hi

I have looked at the workday function but this doesn't seem to give me what I would be looking for - unless I'm missing something?

I need excel to look produce something along these lines

Resource Name - J F M A M J J etc as columns

and then split the work remaining using the start and end date?

N

 

[SkipVought]

Hi,

Please be much more descriptive.

Please post some sample data in the columnar format you have.

Pleaase show how SPECIFICALLY, workday function is not working for you.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Joycelet]

Hi

This is how the the data is currently set out:

NJJ 22 days 06/08/2007 08:00 04/09/2007 17:00
NJJ 10 days 06/08/2007 08:00 17/08/2007 17:00
NJJ 1 day 03/09/2007 08:00 03/09/2007 17:00
NJJ 1 day 04/09/2007 08:00 04/09/2007 17:00
NJJ 0 days 04/09/2007 17:00 04/09/2007 17:00
NJJ 15 days 10/09/2007 08:00 28/09/2007 17:00
NJJ 5 days 10/09/2007 08:00 14/09/2007 17:00

How i need it to look is:

Names Jan Feb Mar Apr May
NJJ 1 3.75

Which I can do with a Pivot Table but I need it to work out the days remaining over the timescale not just from the start date

I hope I've supplied enough information and in the correct format?

N

 

[SkipVought]

Hi,

Here's the result for the data you posted...
[tt]
Resource Dur Start End 8/1/2007 9/1/2007 10/1/2007 11/1/2007
NJJ 22 8/6/2007 9/4/2007 20 2 0
NJJ 10 8/6/2007 8/17/2007 10 0 0
NJJ 1 9/3/2007 9/3/2007 0 1 0
NJJ 1 9/4/2007 9/4/2007 0 1 0
NJJ 0 9/4/2007 9/4/2007 0 1 0
NJJ 15 9/10/2007 9/28/2007 0 1 5
NJJ 5 9/10/2007 9/14/2007 0 5 0
[/tt]
Here's the formula in E2...
[tt]
=IF(OR($C2>F$1,$D2<E$1),0,NETWORKDAYS(IF($C2>=E$1,$C2,E$1),IF($D2>F$1,F$1,$D2)))
[/tt]
and using named ranges...
[tt]
=IF(OR(Start>F$1,End<E$1),0,NETWORKDAYS(IF(Start>=E$1,$C2,E$1),IF(End>F$1,F$1,$D2)))
[/tt]



Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Joycelet]

Hi

Thanks for the quick response - that works great for the standard duration. But i have a modified duration that is a calculated field.

How could I get the above formula to look at the calculated duration not the actual duration?

The calucation is:

If Status A then 100% of actual duration
If Status B then 75% of actual duration
If Status C then 25% of actual duration



For example:

Name
NJJ 5 5 24/09/2007 28/09/2007
NJJ 1 1 23/08/2007 23/08/2007
NJJ 5 3.75 17/09/2007 21/09/2007
NJJ 10 7.5 03/09/2007 14/09/2007
NJJ 10 7.5 22/10/2007 02/11/2007
NJJ 10 7.5 07/01/2008 18/01/2008
NJJ 10 2.5 21/01/2008 01/02/2008


So I actually need the second duration to be spread over the start and end months.

Hope this makes sense and I've given enough info again

Thanks for your help

N

 

[SkipVought]

I wish that you would get your requirements straight.

Your example has no relationship to STATUS???
[tt]
For example:

Name
NJJ 5 5 24/09/2007 28/09/2007
NJJ 1 1 23/08/2007 23/08/2007
NJJ 5 3.75 17/09/2007 21/09/2007
NJJ 10 7.5 03/09/2007 14/09/2007
NJJ 10 7.5 22/10/2007 02/11/2007
NJJ 10 7.5 07/01/2008 18/01/2008
NJJ 10 2.5 21/01/2008 01/02/2008
[/tt]
Where is STATUS???

Please be CLEAR, CONCISE and COMPLETE.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Joycelet]

Hi Skip

Sorry for not giving you all the information.

Here is the full spreadsheet:

Name	Status	Probabilty	Baseline	Actual	Start_Date	Finish_Date
NJJ	A	100%	1	1	23/08/2007	23/08/2007
NJJ	B	75%	5	3.75	17/09/2007	21/09/2007
NJJ	B	75%	10	7.5	03/09/2007	14/09/2007
NJJ	B	75%	10	7.5	22/10/2007	02/11/2007
NJJ	B	75%	10	7.5	07/01/2008	18/01/2008
NJJ	C	25%	10	2.5	21/01/2008	01/02/2008
NJJ	C	25%	10	2.5	21/01/2008	01/02/2008
NJJ	C	25%	10	2.5	21/01/2008	01/02/2008

Hope this is OK?

N

 

[SkipVought]

How about just the product of STATUS and the formula I posted?

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[SkipVought]

What I meant to say is use an IF function as a product
[tt]
=if(status="B",.75,if(status="C",.25,1))*IF(OR($C2>F$1,$D2<E$1),0,NETWORKDAYS(IF($C2>=E$1,$C2,E$1),IF($D2>F$1,F$1,$D2)))
[/tt]


Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[Joycelet]

Hi

Thanks for the fast response - I have looked at product and that just what i need, i have created:

=PRODUCT(D3:E3)

How should I combine this with the previous code?


N

 

[Joycelet]

Hi

Thanks - that has worked a treat

N

 

[Joycelet]

Hi Again

I have the following formula now fully tested and working lovely. The result is then /21 which represents days per month and set as a %.


For example if the formula returns 1 day - it will show as 5% in the cell.

What I now need to do if take away the % complete from the result?


=IF(STATUS="FE",0.75,IF(STATUS="E",0.25,1))*IF(OR($D3>H$1,$E3<G$1),0,NETWORKDAYS(IF($D3>=G$1,$D3,G$1),IF($E3>H$1,H$1,$E3)))-F3

When i try and then set this to / by 21 days and set a percentage it's not giving me the correct results. Should be 2.38% but give me 97...% instead?

Thanks

N

 

[xlbo]

Surely this is simple percentage calc

I assume that you get 97.62%

What is 1 - 97.62% ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Joycelet]

Hi

1 is the result of the networkday (start date - finish date) - so 1 day out of 21 is 5%

I then need to take the 5% and if 50% has been completed it should say 2.5% remaining but I cant seem to get it to produce what i need?

Duration % of Month % Complete Remaining
1 4.8% 50.0% 2.4%


I can do it manually but not can't seem to get it to do it in the formula?



N

 

[xlbo]

.....this:

% Of Month x % Complete

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question