Countifs now working with date format

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,175

Office Version

1. 365

Platform

1. Windows

• May 6, 2015

• #2

Welcome to the Board!

Try this:

Code:

[COLOR=#333333]=COUNTIFS(N2:N11253,">=" & DATE(2015,4,1),N2:N11253,"<=" & DATE(2015,4,30))[/COLOR]

 

Upvote 0

Caribeiro77

Well-known Member

Joined

Sep 24, 2010

Messages

1,261

• May 6, 2015

• #3

Welcome, i don't know if you can put the date/time value directly in to the formula, i think you have to change it a little bit:

=COUNTIFS(N2:N11253,">="&date(2015,4,1),N2:N11253,"<="&date(2015,4,30))

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 6, 2015

• #4

agamino said:

I have the current date format and cannot get a count between two dates. Here is my formula:

Click to expand...

What exactly happens with your formula?
Do you get 0, or some other number that is just not the correct #?

Are the dates in N2:N11253 real dates?
Confirm with
=ISNUMBER(N2) and fill down
Are they TRUE or FALSE? Or mix of both?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 6, 2015

• #5

Caribeiro77 said:

Welcome, i don't know if you can put the date/time value directly in to the formula

Click to expand...

You can, as long as it's written in a format consistent with your PC's regional settings for date formats.

So if you wrote it as ">=4/7/2015" and your intention is that represents July 4th, but your PC is using US formats of mm/dd/yyyy
Then it would be read as April 7th.

That's why it is best to avoid that ambiguity by using the DATE function as suggested..

 

Upvote 0

A

agamino

New Member

Joined

May 6, 2015

Messages

29

• May 6, 2015

• #6

I tried this formula:

=COUNTIFS(N2:N11253,">=" & DATE(2015, 4, 1),N2:N11253,"<=" & DATE(2015,4,30))

I still get "0" and it should return 170. I made sure the cells are in date format and not text or number. Not sure why it will not read.

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 6, 2015

• #7

agamino said:

I made sure the cells are in date format and not text or number.

Click to expand...

It does't matter how the cells are 'formatted'.
It only matters if the cells actually 'contain' real dates.
i.e. A cell can be formatted as a Date, yet still contain a text string (and vice versa)

What did the =ISNUMBER(N2) return?

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,175

Office Version

1. 365

Platform

1. Windows

• May 6, 2015

• #8

Are you sure you have dates that fall within that range?
Go to a date that falls in the range you are searching, temporarily change its Format to "General", and let us know what it returns.

 

Upvote 0

Caribeiro77

Well-known Member

Joined

Sep 24, 2010

Messages

1,261

• May 6, 2015

• #9

Ok that's why it doesn't work in my sheets when i use date/time values that some folks post here.....

 

Upvote 0

A

agamino

New Member

Joined

May 6, 2015

Messages

29

• May 6, 2015

• #10

=ISNUMBER(N2:N11253)=FALSE, and yes there are 170 cells in the range.

 

Upvote 0