How to track production target date

[akshay6s]

HI,
i have a production tracking file in which i fill daily production according to date and quality.

in sheet 1 column AH is of date column when order has got executed and column AI is of lead time (how much day it will take 2 complete the order). so when we add lead time in order execution date we get column AM. column AL is daily expected data.

In column AN i want to see date which will be according to today data and same for tomorrow and so on.

for example:- date in am4 is 3/1/2020 but it is according to AL4. that means if we get daily production of 133.33 then it will complete on given date. but as daily production is below 133.33 then the date is getting late so i want to calculate date according to daily production entered.

please give appropriate formula. and if any other option or format requires i will like to have, hoping to get this as solved

[kvsrinivasamurthy]

Not understood requirement. May be this.
In AN4 then copy dowmn.

=$AH4+CEILING($AG4/$AL4,1)

[akshay6s]

first of all thanks a lot for replying on my query. after applying your formula the result is same as column am when we will add column ai in column ah we will get column am. the date in am5 is written by mistake.

my requirement is that when i will enter today production below the date (b4:af) then date should come according to that day. means if there was order of 8000 mtrs.
and execution date is january 1 2020. and lead time is 60 days. then target date in column am will be 3-1-2020. which means we require daily production of 133.33

but after execution of order production is getting low and we are getting only 50 meters (b4). it means our target date has extended automatically because if we will get only 50 meters daily then when our 8000 meters will complete. so i want that date to show in column an.
and same if we get more production from 133.33 our order will get done before column am. for example if we get 150 meters per day. our production will get complete on 2-23-2020 instead of 3-1-2020.

i hope now you will have get the idea what i want. in short, when i will enter the daily production date wise in cell b4:af the date in column an should be fluctuate daily on behalf of entered meters.

[JeteMc]

I am breaking this down into smaller bits so that will be easier to troubleshoot.
AO4 displays the daily production based on the most recent value in columns B:AF using: =INDEX(B4:AF4,AGGREGATE(14,6,(COLUMN(B4:AF4)-COLUMN(A4))/(B4:AF4<>""),1))
AP4 displays the number of days that it will take to complete the remaining amount (difference column) using: =ROUNDUP(AK4/AO4,0)
AM4 displays the changed date using: =SUM(AH4,AP4)
Note that I am unsure that this actually yields an accurate date as it appears that work is not done every day, however that is has more to do with what you want computed as opposed to how it is computed.
Let us know if you have any questions.

[akshay6s]

wow!!!! just amazing. wonderfully done. thanks a lot for your hard work. you are an excel expert in real words.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.