CODEWITHSUNDEEP
exceldateexcel-formula
Michael Crane
Michael Crane

Reputation: 3

Counting Cells with date enter as MM/DD/YYYY, but formatted as Month-YY

I have a document that shows users who have accounts for a company resource, and it shows their last login. The exported data into excel shows as Last Login (Column E) and the cell data is MM/DD/YYYY with a time stamp. Example: 4/15/2015 3:01:59 PM

I have formatted all the cells to read as Month-YY. So that example shows April-15.

I am trying to make a chart to show how many users last logged in by counting the dates. So say 10 people last logged in April-15, and 20 people in June-15.

I have this formula I am trying to use, Table1[Last Login] is column E.

=COUNTIF(Table1[Last Login],"April-15")

That returns 0.

The issue I am running into is the formula is looking at the data in the cell as 4/15/2015 3:01:59 PM, rather than what I formatted it to, April-15. I have a little VB experience, but its been a while since school.

Any help is much appreciated!

Upvotes: 0

Views: 1264

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

The following will count all the dates in month of april of 2015:

=SUMPRODUCT((YEAR(Table1[Last Login]) = 2015)*(MONTH(Table1[Last Login])=4))

You can substitute the hard coded numbers to a cell reference. If you put the date in say G1 then:

=SUMPRODUCT((YEAR(Table1[Last Login]) = YEAR(G1))*(MONTH(Table1[Last Login])=MONTH(G1)))

Upvotes: 2

Related Questions