Need a cumulatived formula

[Ananthi]

I have ordered date ..order co.pletion date and target date.. if an order is completed before target date it's not outstanding otherwise outstanding..I need to know how many orders where outstanding completed the next month and so on ...to get number of outstanding orders by end of each month in a running total.. i.e. if June has 3 outstanding ..n August got 3 new order ( n 2 completed) ..so total outstanding end of August is = June (3)+ Aug(3-2)=4..like wise for each month..

 

[PhilS2520]

Hi Ananthi,

Wow! It is so hard to understand your post. But let me try.

If you want to compare dates say: June 1st 2018 as date1, and August 29th 2018 as date2 then date1 < date2 will be true.

So, if you have a column that indicates if a task is pending or not, then you can compare the task date to the end of the month’s date and see if it is pending at the end of the month.

Then you can add all the pending tasks, and you do not need to worry about the completed tasks.

I hope this helps, if not please reword your question in a more descriptive way.

PS: ordered data is a term used in database technology where it means that the data is “clean”

 

[Ananthi]

Hi..I will describe..my question .. I got orders from January 2018 till August 2018. With order date, order completion date and order expected to complete date( target date). If the order is completed before target date then it's not outstanding otherwise outstanding. What I would like to know is how many were outstanding till end of August from January which is completed after target date.i need like a trend graph from January till August ..please review my example..example..order outstanding in January is 10, order outstanding August 5.( outstanding in august + remaining order from July that was completed in august) I.e. the 10 outstanding of July say 5 is completed in august..so outstanding in august is = 5(10-5)+ 5= 10.

 

[PhilS2520]

Hi Ananthi,

OK, this is a bit better. These are the three conditions that might occure.

If order completed before expected date then the order is “Done.”

If order is not completed but expected date is in the future then the order is “On Target”

If order is not completed and the expected date is in the past, then the order is “Outstanding.”

In ONE cell have the function TODAY() this will return today’s date. It is a volatile function, so you only need one cell otherwise it will slow your system down.

Compare the expected date to this cell if expected date is larger then it’s in the future, and thus the status will be “On Target”.

If expected date is smaller than TODAY() then it’s in the past and thus the order has a status of “Outstanding.”

If the order was done the order has a status of “Done.”

Now you can use the COUNTIF() function, in the statistical group to count the different statutes:

= COUNTIF(where to look, what to look for)

And I hope this helps.