help with counting a date

M

mrsgorman

New member
Joined
Dec 28, 2015
Messages
4
Reaction score
0
Points
0
Location
Ohio
I am trying to calculate how many times a certain year occurs in my spreadsheet ( i.e..1991991, 1992, 1993, etc...)
When I use =countif(a1:a27, "1991"), it counts 0 when there are actually 1991 text. When I use =countif(a1:a26,"<12/31/1991") it gives me everything in the year of 1991 but also 1990,1989, etc.. etc...
does anyone know how to calculate this?
 
Taking your first example, I cant see why it will not count the 1991s unless the cells contain something else as well as 1991. If the something else is anything other than leading or trailing spaces, that cell would not be counted.
In your second one you are asking it to count if the cells are less than 12/31/1991, and dates in 1990 and 1989 are less, so they will be counted. In case your not aware Excel holds all dates as a number, starting with 1 (01/01/1900). Todays date is 42366.
 
How do I fix it

Hercules1946 said:
Taking your first example, I cant see why it will not count the 1991s unless the cells contain something else as well as 1991. If the something else is anything other than leading or trailing spaces, that cell would not be counted.
In your second one you are asking it to count if the cells are less than 12/31/1991, and dates in 1990 and 1989 are less, so they will be counted. In case your not aware Excel holds all dates as a number, starting with 1 (01/01/1900). Todays date is 42366.
Click to expand...


Here is what the cells look like
EMPLOYMENT DATETERMINATION DATETIME LAPSED
October 20, 198210-Mar-1511829
January 13, 198623-Sep-974271
October 1, 19915-May-951312
February 18, 19927-Jan-971785

What I need to happen is count how many where employed during a particular period. (how many were employed during 1982, 1991, etc.. etc...) then I need to count how many were terminated each year.

 
Are the Employment Dates entered exactly as shown, ie as text ?
Are the Termination Dates formatted as dates?
 
That is copied straight from the spreadsheet. Everything is formatted in date format.
 
mrsgorman said:
That is copied straight from the spreadsheet. Everything is formatted in date format.
Click to expand...
For count Year in Column "A" if data formated as date you can try formula. In E1 is condition number of year like '1991'
Code: 
=SUMPRODUCT(--(YEAR(A2:A100)=E1))
If date formated as text you can try
Code: 
=SUMPRODUCT(--(TEXT(A2:A100;"yyyy")="1991"))
I hope you'll answer for other readers of the forum, that you helped advice?
regards
 
Last edited:
You must log in or register to reply here.
Back
Top