• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Logical problem

P

Pofski

Member
Hi,


i'm a bit stuck at the moment with a bit of a logical problem.

https://docs.google.com/file/d/0B0naOkYo4pCmZkNtSHlEUnNWQk0/edit?usp=sharing


Now, in the above file you see that i have a column with due date, a column with Last serviced, and a Column with interval.

I'm looking for a way to keep a record of tasks completed this year, and tasks that are still open this year.


Now Interval is indicative of the months between each scheduled maintenance.

Date last serviced can be updated on the server by somebody who completed a task, and when that date changes, the due date changes depending on the interval.


So i am looking for a way to have a count of still open tasks this year, and the amount of tasks that are completed. I will prolly have to use circular logic to make some sort of log file as well to keep track of the overtime.


Now, not unimportant, this is for a file that is at this moment over 35.000 rows, and growing during the year.


I know this might be a lot at once to ask, but i'm looking for a way to get this started.

i have made something similar to this last month, but the file was different in a way that all open tasks didn't have a completed date filled in, so it was easier to just count all those that have a date, and get a list of completed that way.

for the logging i use something in the trend of

=IF(AND(P3<>"";P3<$B$2);IF(P4="";$B$2;P4);""), and that's working out nicely, but i'm kinda mindblocked on how to do this problem.


Thanks in advance
 
Hi, Pofski!


I don't know if I fully get your point, but is this what you're looking for?

=SI(B2="";"";SI(FECHA.MES(B2;C2)<G$2;0;1)) -----> in english: =IF(B2="","",IF(EDATE(B2,C2)<G$2,0,1))

with 0 for close and 1 for open.


Regards!
 
Hi Pofski,


As far as i have understood you are interested in due dates that are in current dates's year, so you can try:


Code: 
=IF(YEAR(A2)=YEAR($G$2),1,0)


..and drag down.


Regards,
 
Hi again.


first off, thanks for trying to help me out, but i have come to the conclusion that there is not enough data to get the desired result.


Which can also be seen as a good thing, because i think we have found a flaw in our system.

Now we can work on improving it.


Thanks again for trying.


Sincerely
 
Hi Pofski,


Your not one of those Harvard economists are you? :)


http://chandoo.org/forums/topic/an-intresting-read
 
You must log in or register to reply here.
Back
Top