Split The dates into year

[merryperson]

Hi
i have analysed the data I have to produce a table showing the number of cases received and cleared each month based on converting the date field into months.
I now have a problem because the 12 months has gone round and now I have April repeating itself so I need to add the year to the spreadsheet.
I was going to create a hidden column in the date to use the year formula and show whether its 2017 or 2018.I was then going to add the year to the results tab but I am struggling to do this.

Hopefully the example below will show what I am trying to do

This is the results table pulled from columns showing two date columns received and cleared

The formula I have used to collate the data is
=SUMPRODUCT(--(ISNUMBER('CT Work'!$J$2:$J$1058)),--(MONTH('CT Work'!$J$2:$J$1058)=4))
What is the beat way to collate date for April 2018 as i already have April 2018

Month Received Cleared
April 2 3
May 3 5
June 6 7
July 3 8
August 3 4
Sept 5 5
Oct 7 7
Nov 8 9
Dec 8 7
Jan 3 4
Feb 5 7
March 6 7
April 2019

Please advise
Many thanks

 

[wideboydixon]

Why not add a new criteria to the SUMPRODUCT to check the year as well? Like this:

=SUMPRODUCT(--(ISNUMBER('CT Work'!$J$2:$J$1058)),--(MONTH('CT Work'!$J$2:$J$1058)=4),--(YEAR('CT Work'!$J$2:$J$1058)=2018))

WBD

 

[wideboydixon]

Alternatively, set the values in first first column to be 1-Apr-2018, 1-May-2018 ... etc. to 1-Apr-2019 then custom format the column as "mmmm yyyy". Now you can use something like:

=SUMPRODUCT(--(ISNUMBER('CT Work'!$J$2:$J$1058)),--(MONTH('CT Work'!$J$2:$J$1058)=MONTH($A2)),--(YEAR('CT Work'!$J$2:$J$1058)=YEAR($A2)))

Which you can easily copy down the column.

WBD