CODEWITHSUNDEEP
excelexcel-formula
Josh
Josh

Reputation: 137

Excel Using concatenate to compare dates

I have a table containing several columns of which one is a date/time field. I am trying to get a count of instances per day using the following

=COUNTIFS(Table4[Data],"Apple",Table4[Date],(CONCATENATE(V4,"*")))


Data    Date        Comp Date   Count
Apple   6/12/18 1:00 PM     6/12/18 12:00 AM    0
Apple   6/12/18 7:00 AM         
Orange  6/12/18 1:30 PM         
Apple   6/11/18 11:23 AM

From my understanding of all the moving parts here I should be checking to see if "Apple" exists in the data column and then if "6/12/18" with any amount or type of characters after it exists. If both are true I will then get a count + 1 leaving me with a value of 2 in the above example.

What I actually get however is a 0 unless I match the time portion of date the data to be exactly the same and then removed the wildcard ,"*" from the equation.

Am I doing something wrong or can the wildcard not be used to accomplish what I am trying?

Thanks!

Upvotes: 0

Views: 100

Answers (3)

jblood94
jblood94

Reputation: 16981

If your dates are stored as dates instead of text, use the following:

=COUNTIFS(Table4[Data],"Apple",Table4[Date],">="&V4,Table4[Date],"<"&V4+1)

Upvotes: 0

Jortx
Jortx

Reputation: 727

If your [Date] column is a datetime or smalldatetime, you can work with it using CONVERT function, depending on how you want to group.

For example, if you don't care about the time to do the group, you could use the next query:

SELECT CONVERT(varchar,[Date],103), [Data], COUNT(*)
FROM [test_delete].[dbo].[Table1]
GROUP BY CONVERT(varchar,[Date],103), [Data]

This should result in something like this:

[Date]      [Data]      [Count]
11/06/2018  Apple       1
12/06/2018  Apple       2
12/06/2018  Orange      1

Hope this helps you

Upvotes: 0

Jonathan
Jonathan

Reputation: 817

I think you should set your criteria properly.

If you add an additional column next to your Date that contains calculates the integer value of your date using INT() and format the display as DATE (m/d/yyyy) you should then be able to use the following COUNTIFS formula

=COUNTIFS(Table4[Data], "Apple", Table4[Date], "=6/12/18")

See the explanatory video from their Office' support site: https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842

Upvotes: 2

Related Questions