I have a column of combined date and time as text format and i want to count if some data is between to dates. so i write this:
=Countifs(DATE(MID(@Data!A:A,1,4),MID(@Data!A:A,6,2),MID(@Data!A:A,9,2)),">=A7",DATE(MID(@Data!A:A,1,4),MID(@Data!A:A,6,2),MID(@Data!A:A,9,2)),"<=A8")
@Data!A:A is where i want to convert it to date:
@Data!A:A
and then check the if; ">=A7" and "<=A8" are two cells i input custom date ranges. but excel won't accept it and says there is a problem with this formula. How to revise it? any thoughts?
I tried as above and I don't want to make extra columns for separate calculations.
The problem is that the COUNTIF* functions do not accept the dynamic arrays.
Also @ return a single value while you need to have an array to count something within.
This formula can be a solution for you:
=SUM(
(DATE(MID(Data!A1:A10,1,4),MID(Data!A1:A10,6,2),MID(Data!A1:A10,9,2))>=A7)*
(DATE(MID(Data!A1:A10,1,4),MID(Data!A1:A10,6,2),MID(Data!A1:A10,9,2))<=A8)
)
And A:A is not a good range to use it here. Narrow it for the data area only:
=LET(
_r,INDIRECT("Data!A1:A"&MATCH(2,1/(Data!A:A<>""))),
SUM(
(DATE(MID(_r,1,4),MID(_r,6,2),MID(_r,9,2))>=A7)*
(DATE(MID(_r,1,4),MID(_r,6,2),MID(_r,9,2))<=A8)
)
)