# Use the results of one formula into another formula

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

• 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
Jan 3, 2017 at 18:37

``````D3:  =DATE(RIGHT(A3,2)+2000,MID(A3,2,2),MID(A3,4,2))
``````=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