• 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.

Date filter Formula

Z

Zhanibek

New Member
Hello I need help in creating filter formula which should be a filtered information: only action due for closure in Q1 (01-01-2014 to 31-03-2014) and everything what should have been closed before 01-01-2014, but was not. Also finded dates have to mrked as "x".

for more information I have attached file where in Main data sheet in Column D of "closure date in Q1" this filter formula have to be placed.
Also I have smilar column which just counts dates which were closed in q1, the formula in Column D should be with same format.

Thanks in Advance!
 

Attachments

  • BOOK1.xls
    894 KB · Views: 10
Hi Zhanibek,

Your explanation is not clear enough to get you a solution. If you can refer to data sheet where the formula should look for data and where to give output than it will be helpful for readers who are reading this post.

Regards,
 
Sorry for poor explonation The mian objective of formula to show dates between 31.01.14 and 31.03.14
and also dates from previous year which still has not been closed which shows as blank cells in Column G from "Main Data" sheet.
This formula have to placed in column D and have to be same format as in Column H due to they have similar function whil here is just counting whcih were closed in Q1 2014.

I need similar formula but with more conditions which have to count Dates between 31.01.14 and 31.03.14 and also previous from year which still open, and dates should not be counted no more tha 31.03.14 (Q1-14).

Bellow I have new attachment more clear than previous one.
 

Attachments

  • BOOK1.xls
    572.5 KB · Views: 6
@Zhanibek

I think you can do it without any additional formula using built-in filter feature, you simply need to :

1. filter "Status" for incomplete entries.
2. Filter column G for dates between your desired range (31 Jan to 31st March 13).

See these snaps.

13.jpg 12.jpg 1.jpg
 
This formula is working but it have to count just till 31-Mar-14 not Q2 or Q3 2014 if to be shrter everything in Column C higher than 31-Mar-14 not in count.
All dates in Q1 2014 and all previous dates before Q1 which were not closed which are blank cells in column G.
 
It is doing exactly the same job, displaying a X whenever:
1. Dates are in Q1 of 2014
2. An uncomplete status in Column F with a blank in column G.

Regards,
 
@Zhanibek,

Regarding your last post, you can still do it by filtering quarters for every thing (incl. the Q1 for 2014) to get the required data.

[What i have understood is that you want want data for "Incomplete Status" uptill the 1st quarter of 2014 that ends on 31st March 2013 If this explanation of your problem is correct you can follow the steps i am referring to]

@SM: Thank for correction dear.
 
@Zhanibek

The credit of 90% of our learning must go to our bosses who force us to do THINGS DIFFERENTLY WITH DIFFICULTIES, where there is an easy task available. :)

@Faseeh : Welcome dear anytime.

Regards,
 
@SM,

...My boss once told me to "Always assign a a task to laziest of the person in the office because the chances are that he will make every effort to reduce the work load and come up with a smart solution.. :)

@deb,

..started Following throttlewroks :D
 
Faseeh said:
@Zhanibek,

Regarding your last post, you can still do it by filtering quarters for every thing (incl. the Q1 for 2014) to get the required data.

[What i have understood is that you want want data for "Incomplete Status" uptill the 1st quarter of 2014 that ends on 31st March 2013 If this explanation of your problem is correct you can follow the steps i am referring to]

@SM: Thank for correction dear.
Click to expand...

But it also have to include dates from previous Years which were not closed, as I mentioned before blank cell in column G it means that did not closed yeat and that cells have to be dispplayed as well .
 
Ok guys I realy agree with you it have to be easy to use. But I still need this formula))
If to be true Im on of the Lazzy employee and I couldnt find any lazzy solution thats why Im here)
 
Here is new attac
Somendra Misra said:
@Zhanibek

Can you post a small sample with say 5-10 different data set showing various situations and desired output.

@Debraj/@Faseeh : What if some NINJA really like the comment of other NINJA :p. @ThrottleWorks is a realy generous person for doing it.


Regards,
Click to expand...
Here we are new attachment

this is the example of how formula have to work.
 

Attachments

  • Book111.xls
    18 KB · Views: 4
Hi Zhanibek!

as per your latest attachment..
Below is working fine..

But now I am missing those UNComplete Complete/Incomplete status.. am I overlooking something.. :(
=IF(LEFT($E2,2)="Q1","X","")
 
Debraj said:
Hi Zhanibek!

as per your latest attachment..
Below is working fine..

But now I am missing those UNComplete Complete/Incomplete status.. am I overlooking something.. :(
=IF(LEFT($E2,2)="Q1","X","")
Click to expand...
Sorry Debraj this formula is not working is counting all dates with Q1((

here I attahced again with complete and incomplete status.
 
Hi,

in your file there is written "not in count" in column J. What does that means? And in column D I am expecting that you have entered your desired result.

Please clarify.

Regards,
 
You must log in or register to reply here.
Back
Top