Countifs formula

[ThedoreHolley]

So I'm trying to count servals cells based off a date. For example If colum A is last name starts will the letter L . Count how many DOB in colum J are within 60 days from today. Some cells are blank and returns the default date 0-jan-00. Which I do not want to count these dates. Any help with a formula would be greatly appreciated.

 

[awoohaw]

So, tell me about the dates that did not get captured? Are those cell blank(absolutely nothing) or do they have a date of day 0 (Dec 31 1899)? What would be your rule for including and excluding the dates in the past. My first example went back 60 days - i'm guessing that specific value was incorrect, but it does demonstrate that there are ways to get other dates provided I have good criteria. Please let me know what that is.

 

[ThedoreHolley]

So, tell me about the dates that did not get captured? Are those cell blank(absolutely nothing) or do they have a date of day 0 (Dec 31 1899)? What would be your rule for including and excluding the dates in the past. My first example went back 60 days - i'm guessing that specific value was incorrect, but it does demonstrate that there are ways to get other dates provided I have good criteria. Please let me know what that is.

So I need to capture all dates that have passed minus the empty slot. Since all this data is pulled from another sheet anything blank adds zero or the default date. But if a date is a legit date I need to account 60 and 90 day losses.

 

[awoohaw]

okay. so regarding getting the date pulled from another sheet....
Is that done via a lookup or index of some kind? If so, you can update that pulling statement to give a value of "" instead of 0.
Can you share an example of the formula that pulls the date?

Secondly, can you confirm that when you say "dates have passed" you mean dates
1. WITHIN today() and Today()+60
or
2. WITHIN today() and Today()+90 (This will also include the records in the first group.

 

[ThedoreHolley]

okay. so regarding getting the date pulled from another sheet....
Is that done via a lookup or index of some kind? If so, you can update that pulling statement to give a value of "" instead of 0.
Can you share an example of the formula that pulls the date?

Secondly, can you confirm that when you say "dates have passed" you mean dates
1. WITHIN today() and Today()+60
or
2. WITHIN today() and Today()+90 (This will also include the records in the first group.

So for pulling dates it is just and =sheetH2 for example. The dates that have pass would be if there was a 1 Feb 23 date for example. The formula would not currently count that in the 60 days. Which I need it to.

 

[awoohaw]

1. To prevent the 0 date from showing up, you can put your date formula (assuming the sheet name is Sheet1) =if(Isblank(Sheet1!H2),"",Sheet1!H2)
2. The formula i gave uses the Today() function, which will change every 24 hours. Which is why 2/23 is no longer included as today is now 2/24. If you want to use a specific date then you either need to 'hard code' it into the formula (which is not recommended) or use a cell to house that comparison date. I asked if you wanted a the 60 counter to be starting from a month end. I also I asked if your defiition of 60 days is two months or a true 60 days (60 days from 31 Dec is 1 March not 28 Jan, for instance).

 

[awoohaw]

You wrote: "The formula would not currently count that in the 60 days. Which I need it to." Please give me the EXACT method you use for wanting to include 23 Feb in the selection? Why do you consider 23 Feb between today() and today()+60, since Today() now equals 24Feb (as of the date of this post)? I don't think you want to use Today as your comparison value in the calculation, please tell me what you use.

 

[ThedoreHolley]

You wrote: "The formula would not currently count that in the 60 days. Which I need it to." Please give me the EXACT method you use for wanting to include 23 Feb in the selection? Why do you consider 23 Feb between today() and today()+60, since Today() now equals 24Feb (as of the date of this post)? I don't think you want to use Today as your comparison value in the calculation, please tell me what you use.

I'm over thinking this problem the last formula you sent works perfectly. Technically I should never have anyone on the tracker pass their DEROS so you solved my problem. Thank you for your help and support!

 

[awoohaw]

You're welcome. if you think one of the posts has provided you with a solution. please mark it as answered.

Best wishes!