extracting month and year from column of dates

N

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
111
Office Version
  1. 2010
Platform
  1. Windows
Hello All,
Hopefully a simple question. I have a column of dates on 2 different sheets (E:E - same column both sheets). These are standard dd/mm/yy entries.
I am hoping to get a formula that I can place in a sheet3 A:A that will check these dates and only list Month and year (Jan-20) once so I end up with a list that will add a new month when it recognises a new date has been entered in sheet1 or sheet2.

From this located in column E:E on sheet 1 and 2
1603759328476.png


To This located on sheet3 A:A
1603759351600.png

The reason for this is sheet3 is an overview of the data relating to these dates in sheet1 and 2 as well as other formulation in sheet3 and graphs

I apologise I am only able to supply images, however I am hoping this is ok

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
I admit it's a very stupid approach,the formula seems too complicated
The difficulty is that the data is distributed across two worksheets，you must use some methods to join memory array together.
If use auxiliary columns or auxiliary worksheet,the method becomes simpler.
Looking forward to better solutions，no auxiliary columns,haha
sheet3
test2.xlsx
ABCDE
1DATE
214-Oct
315-Oct
416-Oct
517-Oct
618-Oct
719-Oct
820-Oct
921-Oct
1022-Oct
1123-Oct
1224-Oct
1325-Oct
1426-Oct
1527-Oct
16 
17 
18 
19 
20 
Sheet3
Cell Formulas
RangeFormula
A2:A20A2=IFERROR(TEXT(INDEX(IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),SMALL(IF(MATCH(IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),)=ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2)),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2)),4^8),ROW(A1))),"[$-en-US]d-mmm;@"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

sheet1
test2.xlsx
ABCDE
1DATE
227-Oct-20
327-Oct-20
427-Oct-20
526-Oct-20
626-Oct-20
725-Oct-20
825-Oct-20
924-Oct-20
1024-Oct-20
1124-Oct-20
1224-Oct-20
1324-Oct-20
1424-Oct-20
1523-Oct-20
1622-Oct-20
1722-Oct-20
1821-Oct-20
1921-Oct-20
2021-Oct-20
21
Sheet1

sheet2
test2.xlsx
ABCDE
1DATE
220-Oct-20
320-Oct-20
420-Oct-20
519-Oct-20
619-Oct-20
718-Oct-20
818-Oct-20
918-Oct-20
1018-Oct-20
1118-Oct-20
1218-Oct-20
1317-Oct-20
1417-Oct-20
1517-Oct-20
1617-Oct-20
1716-Oct-20
1816-Oct-20
1916-Oct-20
2015-Oct-20
2114-Oct-20
Sheet2
 
Upvote 0
With Power Query, bring each table into the PQ editor. Append one to the other. Then delete duplicates and sort.

Power Query: 
let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"DATE", Order.Ascending}})
in
    #"Sorted Rows"
 
Upvote 0
How do you end up with Mar-20 and Apr-20 in the results when there are no dates in March or April 2020 in your original list of dates?
 
Upvote 0
Hello all and thanks for the responses so far.

hsnd24_CN - You have listed ALL dates that have occurred in sheet3. This is not quite what I am after.
alansidman - unfortunately I do not have access to Power Query
Peter_SSs - this is a sample I pulled. The current data in sheet 1 and 2 which is being refenced is over 800 rows and growing.

When I manually enter Oct-20 into respective cell in column A sheet3 (A8 using the supplied example part of Month-Year) other formulas on the respective row start populating as they are dependant on

As I am still a learner when it come to Excel, is it possible to have something similar to the following:

IF Sheet1 E:E OR Sheet2 E:E contains a date ranging between 01-Oct-2020 and 31-Oct-2020 then place Oct-20 in the cell otherwise leave blank.

The idea is that when a date in the formula range is entered into sheet1 E:E or sheet2 E:E then sheet3 will auto populate. This then will automate sheet3 without manual intervention.

This would be placed in the relative cell I want Oct-20 to appear, and I could then adjust as required for Dec-20, Jan-21 etc.
This would also make it easier if other sheets were created requiring the same data to be captured (god I hope not) by adding the relative sheet information to the OR put of the formula

I understand that it is difficult to come up with something without the actual data in front of you, however due to what it is, I am unable to do this and am grateful for any and all assistance
 
Upvote 0
Power Query is a free add in to Excel 2010.

About Power Query in Excel - Microsoft Support

With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.
support.microsoft.com support.microsoft.com
 
Upvote 0
Hi alan,
Yes it is, however I am on a network that will not allow addons or anything beyond what I am supplied with. This also means I am unable to send the data to my personal email.

Its a case of doing the best I can with what I have available (and having my hands tied). Trust me, it is very frustrating.
 
Upvote 0
you need to add helper columns to add the month and year. that's how i do it. use the following formula on Sheet3 =text(sheet1.E:E Cell, "MMM") for month and then add another column on Sheet3 fore the year =text(sheet1, cell where date is, "YYYY") and then copy the formula all the way down. good luck.
 
Upvote 0
Solution
I have put this together to show the month and year result I am chasing. As can be seen, column A only has a month and year reference. The data in the remaining Columns is the number of occurrences between the start and end of the Months. These are then converted to graphs. The data in columns B onwards will populate only when something is placed in Column A.
CALL INS - 7 SHOP - WITH STATS..xlsx
ABCDEFGHIJKLM
3TOTALS143651850111784312761
4Mar-20000000000100
5Apr-20900000110510
6May-202926121700661051
7Jun-202411000015126100
8Jul-2030162105125130000
9Aug-202893400760000
10Sep-20162115601144000
11Oct-207104001311000
12            
13            
14            
15            
16            
17            
18            
19            
20            
BRS Stats
Cell Formulas
RangeFormula
B3:M3B3=SUM(B4:B37)
B4B4=IF(A4="","",COUNTIFS(AABD!$E$3:$E$90,">=1/3/2020",AABD!$E$3:$E$90,"<=30/03/2020"))
C4C4=IF(A4="","",COUNTIFS(AABD!$H$3:$H$90,">=1/3/2020",AABD!$H$3:$H$90,"<=30/03/2020"))
D4D4=IF(A4="","",COUNTIFS(AABD!$I$3:$I$90,">=1/4/2020",AABD!$I$3:$I$90,"<=30/04/2020"))
E4E4=IF(A4="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/03/2020",NBAN!$E$3:$E$35,"<=30/03/2020"))
F4F4=IF(A4="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/3/2020",NBAN!$H$3:$H$35,"<=30/03/2020"))
G4G4=IF(A4="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/3/2020",NBAN!$I$3:$I$35,"<=30/03/2020"))
H4H4=IF(A4="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/3/2020",'SCA Transfers'!$G$3:$G$150,"<=30/03/2020"))
I4I4=IF(A4="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/3/2020",'SCA Transfers'!$I$3:$I$150,"<=30/03/2020"))
J4J4=IF(A4="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/3/2020",'SCA Transfers'!$J$3:$J$150,"<=30/03/2020"))
K4K4=IF(A4="","",COUNTIFS(IDT!$H$3:$H$90,">=1/3/2020",IDT!$H$3:$H$90,"<=30/03/2020"))
L4L4=IF(A4="","",COUNTIFS(IDT!$K$3:$K$90,">=1/3/2020",IDT!$K$3:$K$90,"<=30/03/2020"))
M4M4=IF(A4="","",COUNTIFS(IDT!$L$3:$L$90,">=1/3/2020",IDT!$L$3:$L$90,"<=30/03/2020"))
B5B5=IF(A5="","",COUNTIFS(AABD!$E$3:$E$90,">=1/4/2020",AABD!$E$3:$E$90,"<=30/04/2020"))
C5C5=IF(A5="","",COUNTIFS(AABD!$H$3:$H$90,">=1/4/2020",AABD!$H$3:$H$90,"<=30/04/2020"))
D5D5=IF(A5="","",COUNTIFS(AABD!$I$3:$I$90,">=1/3/2020",AABD!$I$3:$I$90,"<=30/03/2020"))
E5E5=IF(A5="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/4/2020",NBAN!$E$3:$E$35,"<=30/04/2020"))
F5F5=IF(A5="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/4/2020",NBAN!$H$3:$H$35,"<=30/04/2020"))
G5G5=IF(A5="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/4/2020",NBAN!$I$3:$I$35,"<=30/04/2020"))
H5H5=IF(A5="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/4/2020",'SCA Transfers'!$G$3:$G$150,"<=30/04/2020"))
I5I5=IF(A5="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/4/2020",'SCA Transfers'!$I$3:$I$150,"<=30/04/2020"))
J5J5=IF(A5="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/4/2020",'SCA Transfers'!$J$3:$J$150,"<=30/04/2020"))
K5K5=IF(A5="","",COUNTIFS(IDT!$H$3:$H$90,">=1/4/2020",IDT!$H$3:$H$90,"<=30/04/2020"))
L5L5=IF(A5="","",COUNTIFS(IDT!$K$3:$K$90,">=1/4/2020",IDT!$K$3:$K$90,"<=30/04/2020"))
M5M5=IF(A5="","",COUNTIFS(IDT!$L$3:$L$90,">=1/4/2020",IDT!$L$3:$L$90,"<=30/04/2020"))
B6B6=IF(A6="","",COUNTIFS(AABD!$E$3:$E$90,">=1/5/2020",AABD!$E$3:$E$90,"<=30/05/2020"))
C6C6=IF(A6="","",COUNTIFS(AABD!$H$3:$H$90,">=1/5/2020",AABD!$H$3:$H$90,"<=31/05/2020"))
D6D6=IF(A6="","",COUNTIFS(AABD!$I$3:$I$90,">=1/5/2020",AABD!$I$3:$I$90,"<=31/05/2020"))
E6E6=IF(A6="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/5/2020",NBAN!$E$3:$E$35,"<=31/05/2020"))
F6F6=IF(A6="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/5/2020",NBAN!$H$3:$H$35,"<=31/05/2020"))
G6G6=IF(A6="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/5/2020",NBAN!$I$3:$I$35,"<=31/05/2020"))
H6H6=IF(A6="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/5/2020",'SCA Transfers'!$G$3:$G$150,"<=31/05/2020"))
I6I6=IF(A6="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/5/2020",'SCA Transfers'!$I$3:$I$150,"<=31/05/2020"))
J6J6=IF(A6="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/5/2020",'SCA Transfers'!$J$3:$J$150,"<=31/05/2020"))
K6K6=IF(A6="","",COUNTIFS(IDT!$H$3:$H$90,">=1/5/2020",IDT!$H$3:$H$90,"<=30/05/2020"))
L6L6=IF(A6="","",COUNTIFS(IDT!$K$3:$K$90,">=1/5/2020",IDT!$K$3:$K$90,"<=30/05/2020"))
M6M6=IF(A6="","",COUNTIFS(IDT!$L$3:$L$90,">=1/5/2020",IDT!$L$3:$L$90,"<=30/05/2020"))
B7B7=IF(A7="","",COUNTIFS(AABD!$E$3:$E$90,">=1/6/2020",AABD!$E$3:$E$90,"<=30/06/2020"))
C7C7=IF(A7="","",COUNTIFS(AABD!$H$3:$H$90,">=1/6/2020",AABD!$H$3:$H$90,"<=30/06/2020"))
D7D7=IF(A7="","",COUNTIFS(AABD!$I$3:$I$90,">=1/6/2020",AABD!$I$3:$I$90,"<=30/06/2020"))
E7E7=IF(A7="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/6/2020",NBAN!$E$3:$E$35,"<=31/06/2020"))
F7F7=IF(A7="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/6/2020",NBAN!$H$3:$H$35,"<=31/06/2020"))
G7G7=IF(A7="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/6/2020",NBAN!$I$3:$I$35,"<=31/06/2020"))
H7H7=IF(A7="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/6/2020",'SCA Transfers'!$G$3:$G$150,"<=30/06/2020"))
I7I7=IF(A7="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/6/2020",'SCA Transfers'!$I$3:$I$150,"<=30/06/2020"))
J7J7=IF(A7="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/6/2020",'SCA Transfers'!$J$3:$J$150,"<=30/06/2020"))
K7K7=IF(A7="","",COUNTIFS(IDT!$H$3:$H$90,">=1/6/2020",IDT!$H$3:$H$90,"<=30/06/2020"))
L7L7=IF(A7="","",COUNTIFS(IDT!$K$3:$K$90,">=1/6/2020",IDT!$K$3:$K$90,"<=30/06/2020"))
M7M7=IF(A7="","",COUNTIFS(IDT!$L$3:$L$90,">=1/6/2020",IDT!$L$3:$L$90,"<=30/06/2020"))
B8B8=IF(A8="","",COUNTIFS(AABD!$E$3:$E$150,">=1/7/2020",AABD!$E$3:$E$150,"<=31/07/2020"))
C8C8=IF(A8="","",COUNTIFS(AABD!$H$3:$H$90,">=1/7/2020",AABD!$H$3:$H$90,"<=31/07/2020"))
D8D8=IF(A8="","",COUNTIFS(AABD!$I$3:$I$90,">=1/7/2020",AABD!$I$3:$I$90,"<=31/07/2020"))
E8E8=IF(A8="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/7/2020",NBAN!$E$3:$E$35,"<=31/07/2020"))
F8F8=IF(A8="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/7/2020",NBAN!$H$3:$H$35,"<=31/07/2020"))
G8G8=IF(A8="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/7/2020",NBAN!$I$3:$I$35,"<=31/07/2020"))
H8H8=IF(A8="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/7/2020",'SCA Transfers'!$G$3:$G$150,"<=31/07/2020"))
I8I8=IF(A8="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/7/2020",'SCA Transfers'!$I$3:$I$150,"<=31/07/2020"))
J8J8=IF(A8="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/7/2020",'SCA Transfers'!$J$3:$J$150,"<=31/07/2020"))
K8K8=IF(A8="","",COUNTIFS(IDT!$H$3:$H$90,">=1/7/2020",IDT!$H$3:$H$90,"<=31/07/2020"))
L8L8=IF(A8="","",COUNTIFS(IDT!$K$3:$K$90,">=1/7/2020",IDT!$K$3:$K$90,"<=31/07/2020"))
M8M8=IF(A8="","",COUNTIFS(IDT!$L$3:$L$90,">=1/7/2020",IDT!$L$3:$L$90,"<=31/07/2020"))
B9B9=IF(A9="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/8/2020",AABD!$E$3:$E$1500,"<=31/08/2020"))
C9C9=IF(A9="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/8/2020",AABD!$H$3:$H$1500,"<=31/08/2020"))
D9D9=IF(A9="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/8/2020",AABD!$I$3:$I$1500,"<=31/08/2020"))
E9E9=IF(A9="","",COUNTIFS(NBAN!$E$3:$E$155,">=1/8/2020",NBAN!$E$3:$E$155,"<=31/08/2020"))
F9F9=IF(A9="","",COUNTIFS(NBAN!$H$3:$H$155,">=1/8/2020",NBAN!$H$3:$H$155,"<=31/08/2020"))
G9G9=IF(A9="","",COUNTIFS(NBAN!$I$3:$I$155,">=1/8/2020",NBAN!$I$3:$I$155,"<=31/08/2020"))
H9H9=IF(A9="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/8/2020",'SCA Transfers'!$G$3:$G$150,"<=31/08/2020"))
I9I9=IF(A9="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/8/2020",'SCA Transfers'!$I$3:$I$150,"<=31/08/2020"))
J9J9=IF(A9="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/8/2020",'SCA Transfers'!$J$3:$J$150,"<=31/08/2020"))
K9K9=IF(A9="","",COUNTIFS(IDT!$H$3:$H$90,">=1/8/2020",IDT!$H$3:$H$90,"<=31/08/2020"))
L9L9=IF(A9="","",COUNTIFS(IDT!$K$3:$K$90,">=1/8/2020",IDT!$K$3:$K$90,"<=31/08/2020"))
M9M9=IF(A9="","",COUNTIFS(IDT!$L$3:$L$90,">=1/8/2020",IDT!$L$3:$L$90,"<=31/08/2020"))
B10B10=IF(A10="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/9/2020",AABD!$E$3:$E$1500,"<=30/09/2020"))
C10C10=IF(A10="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/9/2020",AABD!$H$3:$H$1500,"<=30/09/2020"))
D10D10=IF(A10="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/9/2020",AABD!$I$3:$I$1500,"<=30/09/2020"))
E10E10=IF(A10="","",COUNTIFS(NBAN!$E$3:$E$155,">=1/9/2020",NBAN!$E$3:$E$155,"<=30/09/2020"))
F10F10=IF(A10="","",COUNTIFS(NBAN!$H$3:$H$155,">=1/9/2020",NBAN!$H$3:$H$155,"<=30/09/2020"))
G10G10=IF(A10="","",COUNTIFS(NBAN!$I$3:$I$155,">=1/9/2020",NBAN!$I$3:$I$155,"<=30/09/2020"))
H10H10=IF(A10="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/9/2020",'SCA Transfers'!$G$3:$G$150,"<=30/09/2020"))
I10I10=IF(A10="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/9/2020",'SCA Transfers'!$I$3:$I$150,"<=30/09/2020"))
J10J10=IF(A10="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/9/2020",'SCA Transfers'!$J$3:$J$150,"<=30/09/2020"))
K10K10=IF(A10="","",COUNTIFS(IDT!$H$3:$H$90,">=1/9/2020",IDT!$H$3:$H$90,"<=31/09/2020"))
L10L10=IF(A10="","",COUNTIFS(IDT!$K$3:$K$90,">=1/9/2020",IDT!$K$3:$K$90,"<=31/09/2020"))
M10M10=IF(A10="","",COUNTIFS(IDT!$L$3:$L$90,">=1/9/2020",IDT!$L$3:$L$90,"<=31/09/2020"))
B11B11=IF(A11="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/10/2020",AABD!$E$3:$E$1500,"<=31/10/2020"))
C11C11=IF(A11="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/10/2020",AABD!$H$3:$H$1500,"<=31/10/2020"))
D11D11=IF(A11="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/10/2020",AABD!$I$3:$I$1500,"<=31/10/2020"))
E11E11=IF(A11="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/10/2020",NBAN!$E$3:$E$1500,"<=31/10/2020"))
F11F11=IF(A11="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/10/2020",NBAN!$H$3:$H$1500,"<=31/10/2020"))
G11G11=IF(A11="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/10/2020",NBAN!$I$3:$I$1500,"<=31/10/2020"))
H11H11=IF(A11="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/10/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/10/2020"))
I11I11=IF(A11="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/10/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/10/2020"))
J11J11=IF(A11="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/10/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/10/2020"))
K11K11=IF(A11="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/10/2020",IDT!$H$3:$H$1500,"<=31/10/2020"))
L11L11=IF(A11="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/10/2020",IDT!$K$3:$K$1500,"<=31/10/2020"))
M11M11=IF(A11="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/10/2020",IDT!$L$3:$L$1500,"<=31/10/2020"))
B12B12=IF(A12="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/11/2020",AABD!$E$3:$E$1500,"<=31/11/2020"))
C12C12=IF(A12="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/11/2020",AABD!$H$3:$H$1500,"<=31/11/2020"))
D12D12=IF(A12="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/11/2020",AABD!$I$3:$I$1500,"<=31/11/2020"))
E12E12=IF(A12="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/11/2020",NBAN!$E$3:$E$1500,"<=31/11/2020"))
F12F12=IF(A12="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/11/2020",NBAN!$H$3:$H$1500,"<=31/11/2020"))
G12G12=IF(A12="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/11/2020",NBAN!$I$3:$I$1500,"<=31/11/2020"))
H12H12=IF(A12="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/11/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/11/2020"))
I12I12=IF(A12="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/11/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/11/2020"))
J12J12=IF(A12="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/11/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/11/2020"))
K12K12=IF(A12="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/11/2020",IDT!$H$3:$H$1500,"<=31/11/2020"))
L12L12=IF(A12="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/11/2020",IDT!$K$3:$K$1500,"<=31/11/2020"))
M12M12=IF(A12="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/11/2020",IDT!$L$3:$L$1500,"<=31/11/2020"))
B13B13=IF(A13="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/12/2020",AABD!$E$3:$E$1500,"<=31/12/2020"))
C13C13=IF(A13="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/12/2020",AABD!$H$3:$H$1500,"<=31/12/2020"))
D13D13=IF(A13="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/12/2020",AABD!$I$3:$I$1500,"<=31/12/2020"))
E13E13=IF(A13="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/12/2020",NBAN!$E$3:$E$1500,"<=31/12/2020"))
F13F13=IF(A13="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/12/2020",NBAN!$H$3:$H$1500,"<=31/12/2020"))
G13G13=IF(A13="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/12/2020",NBAN!$I$3:$I$1500,"<=31/12/2020"))
H13H13=IF(A13="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/12/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/12/2020"))
I13I13=IF(A13="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/12/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/12/2020"))
J13J13=IF(A13="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/12/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/12/2020"))
K13K13=IF(A13="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/12/2020",IDT!$H$3:$H$1500,"<=31/12/2020"))
L13L13=IF(A13="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/12/2020",IDT!$K$3:$K$1500,"<=31/12/2020"))
M13M13=IF(A13="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/12/2020",IDT!$L$3:$L$1500,"<=31/12/2020"))
B14B14=IF(A14="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/1/2021",AABD!$E$3:$E$1500,"<=31/1/2021"))
C14C14=IF(A14="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/1/2021",AABD!$H$3:$H$1500,"<=31/1/2021"))
D14D14=IF(A14="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/1/2021",AABD!$I$3:$I$1500,"<=31/1/2021"))
E14E14=IF(A14="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/1/2021",NBAN!$E$3:$E$1500,"<=31/1/2021"))
F14F14=IF(A14="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/1/2021",NBAN!$H$3:$H$1500,"<=31/1/2021"))
G14G14=IF(A14="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/1/2021",NBAN!$I$3:$I$1500,"<=31/1/2021"))
H14H14=IF(A14="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/1/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/1/2021"))
I14:I15I14=IF(A14="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/1/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/1/2021"))
J14:J15J14=IF(A14="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/1/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/1/2021"))
K14K14=IF(A14="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/1/2021",IDT!$H$3:$H$1500,"<=31/1/2021"))
L14L14=IF(A14="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/1/2021",IDT!$K$3:$K$1500,"<=31/1/2021"))
M14M14=IF(A14="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/1/2021",IDT!$L$3:$L$1500,"<=31/1/2021"))
B15B15=IF(A15="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/2/2021",AABD!$E$3:$E$1500,"<=28/2/2021"))
C15C15=IF(A15="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/2/2021",AABD!$H$3:$H$1500,"<=28/2/2021"))
D15D15=IF(A15="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/2/2021",AABD!$I$3:$I$1500,"<=28/2/2021"))
E15E15=IF(A15="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/2/2021",NBAN!$E$3:$E$1500,"<=28/2/2021"))
F15F15=IF(A15="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/2/2021",NBAN!$H$3:$H$1500,"<=28/2/2021"))
G15G15=IF(A15="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/2/2021",NBAN!$I$3:$I$1500,"<=28/2/2021"))
H15H15=IF(A15="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/2/2021",'SCA Transfers'!$G$3:$G$1500,"<=28/2/2021"))
K15K15=IF(A15="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/2/2021",IDT!$H$3:$H$1500,"<=28/2/2021"))
L15L15=IF(A15="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/2/2021",IDT!$K$3:$K$1500,"<=28/2/2021"))
M15M15=IF(A15="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/2/2021",IDT!$L$3:$L$1500,"<=28/2/2021"))
B16B16=IF(A16="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/3/2021",AABD!$E$3:$E$1500,"<=30/03/2021"))
C16C16=IF(A16="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/3/2021",AABD!$H$3:$H$1500,"<=30/03/2021"))
D16D16=IF(A16="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/4/2021",AABD!$I$3:$I$1500,"<=30/04/2021"))
E16E16=IF(A16="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/03/2021",NBAN!$E$3:$E$1500,"<=30/03/2021"))
F16F16=IF(A16="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/3/2021",NBAN!$H$3:$H$1500,"<=30/03/2021"))
G16G16=IF(A16="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/3/2021",NBAN!$I$3:$I$1500,"<=30/03/2021"))
H16H16=IF(A16="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/3/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/03/2021"))
I16I16=IF(A16="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/3/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/03/2021"))
J16J16=IF(A16="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/3/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/03/2021"))
K16K16=IF(A16="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/3/2021",IDT!$H$3:$H$1500,"<=30/03/2021"))
L16L16=IF(A16="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/3/2021",IDT!$K$3:$K$1500,"<=30/03/2021"))
M16M16=IF(A16="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/3/2021",IDT!$L$3:$L$1500,"<=30/03/2021"))
B17B17=IF(A17="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/4/2021",AABD!$E$3:$E$1500,"<=30/04/2021"))
C17C17=IF(A17="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/4/2021",AABD!$H$3:$H$1500,"<=30/04/2021"))
D17D17=IF(A17="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/3/2021",AABD!$I$3:$I$1500,"<=30/03/2021"))
E17E17=IF(A17="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/4/2021",NBAN!$E$3:$E$1500,"<=30/04/2021"))
F17F17=IF(A17="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/4/2021",NBAN!$H$3:$H$1500,"<=30/04/2021"))
G17G17=IF(A17="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/4/2021",NBAN!$I$3:$I$1500,"<=30/04/2021"))
H17H17=IF(A17="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/4/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/04/2021"))
I17I17=IF(A17="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/4/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/04/2021"))
J17J17=IF(A17="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/4/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/04/2021"))
K17K17=IF(A17="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/4/2021",IDT!$H$3:$H$1500,"<=30/04/2021"))
L17L17=IF(A17="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/4/2021",IDT!$K$3:$K$1500,"<=30/04/2021"))
M17M17=IF(A17="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/4/2021",IDT!$L$3:$L$1500,"<=30/04/2021"))
B18B18=IF(A18="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/5/2021",AABD!$E$3:$E$1500,"<=30/05/2021"))
C18C18=IF(A18="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/5/2021",AABD!$H$3:$H$1500,"<=31/05/2021"))
D18D18=IF(A18="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/5/2021",AABD!$I$3:$I$1500,"<=31/05/2021"))
E18E18=IF(A18="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/5/2021",NBAN!$E$3:$E$1500,"<=31/05/2021"))
F18F18=IF(A18="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/5/2021",NBAN!$H$3:$H$1500,"<=31/05/2021"))
G18G18=IF(A18="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/5/2021",NBAN!$I$3:$I$1500,"<=31/05/2021"))
H18H18=IF(A18="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/5/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/05/2021"))
I18I18=IF(A18="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/5/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/05/2021"))
J18J18=IF(A18="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/5/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/05/2021"))
K18K18=IF(A18="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/5/2021",IDT!$H$3:$H$1500,"<=30/05/2021"))
L18L18=IF(A18="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/5/2021",IDT!$K$3:$K$1500,"<=30/05/2021"))
M18M18=IF(A18="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/5/2021",IDT!$L$3:$L$1500,"<=30/05/2021"))
B19B19=IF(A19="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/6/2021",AABD!$E$3:$E$1500,"<=30/06/2021"))
C19C19=IF(A19="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/6/2021",AABD!$H$3:$H$1500,"<=30/06/2021"))
D19D19=IF(A19="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/6/2021",AABD!$I$3:$I$1500,"<=30/06/2021"))
E19E19=IF(A19="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/6/2021",NBAN!$E$3:$E$1500,"<=31/06/2021"))
F19F19=IF(A19="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/6/2021",NBAN!$H$3:$H$1500,"<=31/06/2021"))
G19G19=IF(A19="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/6/2021",NBAN!$I$3:$I$1500,"<=31/06/2021"))
H19H19=IF(A19="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/6/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/06/2021"))
I19I19=IF(A19="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/6/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/06/2021"))
J19J19=IF(A19="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/6/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/06/2021"))
K19K19=IF(A19="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/6/2021",IDT!$H$3:$H$1500,"<=30/06/2021"))
L19L19=IF(A19="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/6/2021",IDT!$K$3:$K$1500,"<=30/06/2021"))
M19M19=IF(A19="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/6/2021",IDT!$L$3:$L$1500,"<=30/06/2021"))
B20B20=IF(A20="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/7/2021",AABD!$E$3:$E$1500,"<=31/07/2021"))
C20C20=IF(A20="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/7/2021",AABD!$H$3:$H$1500,"<=31/07/2021"))
D20D20=IF(A20="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/7/2021",AABD!$I$3:$I$1500,"<=31/07/2021"))
E20E20=IF(A20="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/7/2021",NBAN!$E$3:$E$1500,"<=31/07/2021"))
F20F20=IF(A20="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/7/2021",NBAN!$H$3:$H$1500,"<=31/07/2021"))
G20G20=IF(A20="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/7/2021",NBAN!$I$3:$I$1500,"<=31/07/2021"))
H20H20=IF(A20="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/7/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/07/2021"))
I20I20=IF(A20="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/7/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/07/2021"))
J20J20=IF(A20="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/7/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/07/2021"))
K20K20=IF(A20="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/7/2021",IDT!$H$3:$H$1500,"<=31/07/2021"))
L20L20=IF(A20="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/7/2021",IDT!$K$3:$K$1500,"<=31/07/2021"))
M20M20=IF(A20="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/7/2021",IDT!$L$3:$L$1500,"<=31/07/2021"))

The data is being pulled from 4 other sheets. However I am starting with Sheet1 and Sheet2 to try and establish a formula, which (hopefully) I can modify the cover the other 2. The example in the original post showing the full date in Sheet1 E:E and Shet2 E:E is where the primary data is starting.
I am hoping this clears this somewhat.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Next number from a reference list
Replies
1
Views
220
AlphaFrog
AlphaFrog
Red over White
  • Solved
Filter by specific days, months and years
Replies
3
Views
271
Red over White
Red over White
S
  • Solved
Conditional Formatting for when the month/year date for the same ID on two worksheets doesn't match
Replies
7
Views
126
etaf
etaf
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
79
Ali M
A
R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
299
NoSparks
N

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top