HELP with COUNTIFS :( I have a single hair left, please save it

[RichardX720]

I have an excel document that I created on OSX Excel 2016. I no longer have a Mac and now using Windows Excel 2019. I opened the sheet and added more content for it to calculate and everything went to 0's...

I cannot figure it out why.. I only pasted a few lines of VData below but you get the example.. Its USED to count how many times "UserName 1" showed up in Column C of VData inbetween the dates mentioned checked against Colum D of VData. The cell dates are dd/mm/yyyy and I am using US Region but I have tried to change region to NL and still doesnt work... PLEASE HELP!!

2019-2020_USERS_TESTING.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Doctor	2020	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	UserName 1	0	0	0	0	0	0	0	0	0	0	0	0	0
3	UserName 2	0	0	0	0	0	0	0	0	0	0	0	0	0
4	UserName 3	0	0	0	0	0	0	0	0	0	0	0	0	0
5	UserName 4	0	0	0	0	0	0	0	0	0	0	0	0	0
6	UserName 5	0	0	0	0	0	0	0	0	0	0	0	0	0
7	UserName 6	0	0	0	0	0	0	0	0	0	0	0	0	0
8	UserName 7	0	0	0	0	0	0	0	0	0	0	0	0	0
9	UserName 8	0	0	0	0	0	0	0	0	0	0	0	0	0
10	UserName 9	0	0	0	0	0	0	0	0	0	0	0	0	0
11	UserName 10	0	0	0	0	0	0	0	0	0	0	0	0	0
12	UserName 11	0	0	0	0	0	0	0	0	0	0	0	0	0
13	UserName 12	0	0	0	0	0	0	0	0	0	0	0	0	0
14	UserName 13	0	0	0	0	0	0	0	0	0	0	0	0	0
15	UserName 14	0	0	0	0	0	0	0	0	0	0	0	0	0
16	UserName 15	0	0	0	0	0	0	0	0	0	0	0	0	0
17	UserName 16	0	0	0	0	0	0	0	0	0	0	0	0	0
18	UserName 17	0	0	0	0	0	0	0	0	0	0	0	0	0
19	Totals	0	0	0	0	0	0	0	0	0	0	0	0	0
CONSULTS

Cell Formulas
Range	Formula
B2:B18	B2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/12/2020")
C2:C18	C2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/01/2020")
D2:D18	D2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/02/2020",VData!D:D,"<="&"28/02/2020")
E2:E18	E2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/03/2020",VData!D:D,"<="&"31/03/2020")
F2:F18	F2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/04/2020",VData!D:D,"<="&"30/04/2020")
G2:G18	G2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/05/2020",VData!D:D,"<="&"31/05/2020")
H2:H18	H2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/06/2020",VData!D:D,"<="&"30/06/2020")
I2:I18	I2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/07/2020",VData!D:D,"<="&"31/07/2020")
J2:J18	J2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/08/2020",VData!D:D,"<="&"31/08/2020")
K2:K18	K2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/09/2020",VData!D:D,"<="&"30/09/2020")
L2:L18	L2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/10/2020",VData!D:D,"<="&"31/10/2020")
M2:M18	M2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/11/2020",VData!D:D,"<="&"30/11/2020")
N2:N18	N2	=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/12/2020",VData!D:D,"<="&"31/12/2020")
B19	B19	=SUM([2020])
C19	C19	=SUM([JAN])
D19	D19	=SUM([FEB])
E19	E19	=SUM([MAR])
F19	F19	=SUM([APR])
G19	G19	=SUM([MAY])
H19	H19	=SUM([JUN])
I19	I19	=SUM([JUL])
J19	J19	=SUM([AUG])
K19	K19	=SUM([SEP])
L19	L19	=SUM([OCT])
M19	M19	=SUM([NOV])
N19	N19	=SUM([DEC])

2019-2020_USERS_TESTING.xls
	A	B	C	D	E
13	24	Consult	UserName 1	02/01/2019
14	24	Consult	UserName 12	02/01/2019
15	761	Consult	Username 2	02/01/2019
16	3018	Consult	Username 14	02/01/2019
17	3018	Consult	Username 3	02/01/2019
18	3018	Consult	Username 9	02/01/2019
VData

 

[Fluff]

In that case your dates are text, when a real date is formatted as General you would see a 5 figure number
As date

+Fluff 1.xlsm
	B
2	01/02/2021
3	02/02/2021
4	03/02/2021
5	04/02/2021
6	05/02/2021
7	06/02/2021
8	07/02/2021
Results

as General

+Fluff 1.xlsm
	B
2	44228
3	44229
4	44230
5	44231
6	44232
7	44233
8	44234
Results

 

[RichardX720]

How it looks now if i select the cell..
View attachment 31225

How it looks when I change it to GENERAL
View attachment 31226

SORRY.. correction on this.. It is set to CUSTOM because being on US region I have the date set to dd/mm/yyyy

BUT something odd I do notice.. If I switch to General on any date that is mm/dd/yy it changes to for example 01/01/2019 to 43466 but any date that IS in the EU format 23/12/2019 does not do that...??? I am so confused...

 

[Fluff]

Any date that does not change to a number is text & not a date.

 

[RoryA]

Those are text, not true dates then. Select the column, do Data - Text to Columns, choose delimited, then leave all the options blank in the next step, then in the last step specify that its a date field in DMY order. That should convert them to true dates.

 

[RichardX720]

Ok.. that makes sense.. what I cant seem to do is this then: I have the cell set to CUSTOM dd/mm/yyyy and I type in 22/06/2020 but it stays as text and not as a date...

 

[RichardX720]

Those are text, not true dates then. Select the column, do Data - Text to Columns, choose delimited, then leave all the options blank in the next step, then in the last step specify that its a date field in DMY order. That should convert them to true dates.

I did what you said but it still doesnt seem to work on any dates in the EU format..

 

[RichardX720]

I did what you said but it still doesnt seem to work on any dates in the EU format..

View attachment 31228

NEVERMIND.... didnt select the right date format


Ok so I got them all to show up now as dates.. COUNTIFS still not working

 

[RoryA]

Can you post a workbook? I've never seen that fail before.

 

[RoryA]

Are the dates correct? Are all your COUNTIFS formulas still 0?

 

[RichardX720]

Can you post a workbook? I've never seen that fail before.

Im going to remove all the confidential stuff and send you a scaled down entire version