Seasonal productivity within date range

**JS17** · 03-21-2016, 08:28 AM

Hi,

I would like to produce a % return value based on the start and end dates for a task.

For example.

My task productivity is based on what time of year it is,
between 2 March - 31 May productivity = 90%
between 1 June - 30 Sep productivity = 100%
between 1 Oct - 30 Nov productivity = 90%
between 1 Dec - 1 March productivity = 80%

if task A starts 10 July and ends 30 Aug the productivity would be 100%
If task B starts 1 Sep and Ends 1 Nov then productivity would be averaged (approx 95%)

So I would like to create a formula which would return this % and in turn slightly change the end date of a task (it is a bit of a loop so not sure if that would work)

Hope that is clear

Thanks

JS

**etaf** · 03-21-2016, 09:08 AM

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

how accurate is the average % - if across two % zones is it a simple split ie (%1 + %2)/2 as in the 95% you should
if you go over the cutoff - is it counting the days as a % to work out the new value
and also what do you mean by change the end date slightly

**JS17** · 03-21-2016, 10:05 AM

Thanks for getting back to me.

I have attached a blank template of my workbook with one row filled as an example.
I would like a formula in the 'seasonal productivity' column (the seasonal productivity's are in the second tab)

The accuracy of the % would need to be on a daily basis.

for example if a task ran for 60 days total, 10 days in the 100% productivity range and 50 days in the 90% range the output would be (10*1+50*0.9)/60 = 0.917 (91.7%).

Referring to your comment about 'change the end date slightly', if you look at the workbook, the end date (column L) is generated by inputting a start date and adding the duration (column I). Once the start date and end date have been created, the seasonal productivity % can be calculated, I then want thhis to be reflected in the duration which in turn will change the End date (it is a loop so not sure it will work but thought it may come to an equilibrium if an appropriate formula is used)

A little tricky to explain so hope it makes sense.

Thanks.

JS