Project a date (from prediction calculation)

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

Can someone please help me with a dynamic formula to add
days to a date to be used for projection at an actual
present known rate?

A1 Start Date:
B1 Finish by Date:
C1 Days in between: (eg. 100... from 'networkdays' formula)
D1 Need to accomplish: (eg. 1000)
E1 Accomplish per day: (eg. 10)
(normal operational capacity is 10/day and my B1 date does
not need adusting).
F1: Actual accomplishment:
G1: Today's date:

But, if 50 days have gone by, production was poor and I
have 900 left to do today (Dx is now 900 from D1-F1)) and
Ex is 18/day, how can I add days to B1 to show what is,
today, a reasonable expected completion date under this
senario.

I have been to Chip's 'date and time page' but cannot find
the exact solution to this. I'd prefer to solve this with
a formula rather than using 'XLs scenarios'.

Any help is greatly appreciated.

TIA
Gerry
 
Gerry
Try out the following formula

=WORKDAY(TODAY(),((D1-F1)/E1),holidays

(D1-F1)/E1 represents "the total needed" minus "the total completed" divided by "the production rate (per day)". You might need to adjust this if I interpreted your descriptions incorrectly

The Workday function is basically the inverse of NETWORKDAYS

WORKDAY(start date, days to add, holiday list) = revised dat

Good Luck
Mark Graesse
(e-mail address removed)


----- Gerry wrote: ----

Can someone please help me with a dynamic formula to add
days to a date to be used for projection at an actual
present known rate

A1 Start Date
B1 Finish by Date
C1 Days in between: (eg. 100... from 'networkdays' formula
D1 Need to accomplish: (eg. 1000
E1 Accomplish per day: (eg. 10
(normal operational capacity is 10/day and my B1 date does
not need adusting)
F1: Actual accomplishment
G1: Today's date

But, if 50 days have gone by, production was poor and I
have 900 left to do today (Dx is now 900 from D1-F1)) and
Ex is 18/day, how can I add days to B1 to show what is,
today, a reasonable expected completion date under this
senario

I have been to Chip's 'date and time page' but cannot find
the exact solution to this. I'd prefer to solve this with
a formula rather than using 'XLs scenarios'

Any help is greatly appreciated

TI
Gerr
 
Back
Top