0

I'm trying to have Excel recognize the results of a formula in another formula. I have a list of coded data that combines text and dates. I've extracted both the dates and text in separate columns. In another cell I want to count the amount of times that specific text shows up between certain dates.

So for example in one cell (A3) I have my coded data: A102616. The first character is what I want to track and can either be text or a number in some cases. The next 6 characters represent the date October 26th 2016. I am using the following formulas to split up the data and then convert the numbers to a date.

In cell A3: A102616
In cell B3: =LEFT(A3,1) which extracts the first character "A".
In cell C3: =MID(A3,2,6) which extracts the next 6 characters "102616"
In cell D3: =MID(C3,1,2)&"/"&MID(C3,3,2)&"/"&MID(C3,5,2) which changes it to a date format "10/26/16"

What I want to do is count specific characters in Column B and column D3 that fall within a date range that I specify.

This is the second part of the formula that I was using:

In cell J3 is the start date: 1/3/16
In cell L3 is the end date: 12/3/16
In cell K6 is the character I'm counting: A =COUNTIFS(B:B,K6,D:D,">="&L3)

Can anyone help me with this?

Thanks, Crystal

Improve this question
1
  • 1
    When you do something like =MID(C3,1,2)&"/"&MID(C3,3,2)&"/"&MID(C3,5,2), you make a string. You're trying to do date comparisons on a string, which won't quite work. If you surround the formula in =DATEVALUE, it should work. Your formula would instead be =DATEVALUE(MID(C3,1,2)&"/"&MID(C3,3,2)&"/"&MID(C3,5,2)). This coerces it to a number, and allows it to do numerical comparisons to your end date, provided that your end date is also a number.
    – Joe
    Commented Jan 3, 2017 at 18:37

1 Answer 1

Reset to default
0

For your date extraction, to create a "real" date and not a string, I suggest:

D3:  =DATE(RIGHT(A3,2)+2000,MID(A3,2,2),MID(A3,4,2))

For your "Count" formula:

=COUNTIFS(B:B,Character,D:D,">=" & StartDate,D:D,"<=" &EndDate)

Note: If you might have dates in the 1900's, you will need to modify the DATE formula to take that into account

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .