Help with COUNTIFS formula when counting dates that include a time

[UpToTheGills]

Morning all,

I am attempting to use COUNTIFS to total the number of trades for a particular month on a different worksheet but the formula I have used just returns a zero. I wondering if that is because I need to take account of the dates to count also include the time?

=COUNTIFS('IC Live 4004xxxx'!$B$3:$B$353,">="&C5,'IC Live 4004xxxx'!$B$3:$B$353,"<="&EOMONTH(C5,0))

I can't figure out what is incorrect about the above formula…

The dates that I want to count appear formatted as below:

Thank you for any assistance.

 

[etaf]

whats in C5 ?
does that have a time
other wise should not matter as the time is a decimal - so if c5 is 1/2/22
44593

then any time is greater
so 1/2/22 at 9:00 is
44593.375

but if C5 has a time , then that will make a difference
also are the cells real dates in B, if you format as general do they stay as dates or change to numbers, if stay as dates then they are text

i have reformatted C5 and B3 s general to show the date/time asa number - so you can see what i mean

=COUNTIFS($B$3:$B$353,">="&C5,$B$3:$B$353,"<="&EOMONTH(C5,0))

also changed to exclude the sheet name - so you can see it work on one sheet

test11.xlsx
	A	B	C
1	5
2
3		44593.375
4		2/3/22 10:00
5		2/4/22 0:00	44593
6		2/5/22 0:00
7		2/6/22 0:00
Sheet2

Cell Formulas
Range	Formula
A1	A1	=COUNTIFS($B$3:$B$353,">="&C5,$B$3:$B$353,"<="&EOMONTH(C5,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[UpToTheGills]

Thanks for your reply.

No C5 is simply formatted as MMM YYYY, with the cell containing 01/05/2022:

 

[etaf]

sorry i edited my my post above - what about B
have a reread

 

[UpToTheGills]

Yes, I was trying to google a solution and came across this YouTube video which made the distinction for cells that contain both the date and time since the date is the integer before the decimal point and the time is after the decimal point.

Thanks - let me look at your suggestion.

 

[etaf]

yes thats true
BUT

if you have 1/2/22 in cell b5 - then that is an integer of 44593 - just a number

then you say
($B$3:$B$353,">="&C5

so 1/2/22 at 9:00 is 44593.375
which is greater and so it should count it
as i have shown in the example posted

there are a lot of situations where the time does matter and then you use

INT(date cell)
or if you are counting time say and the cell is time but shows 1/1/1900 - which often happens - you need to remove the INT
so
date cell - Int(date cell) to just get the decimal part as 0.xxxxx

not been able to watch video - as about to leave for the day

 

[UpToTheGills]

Thanks - I am trying to look at installing the XL2BB addin...

So, my formula looks correct yet is returning zero?

=COUNTIFS('IC Live 4004xxxx'!$D$3:$D$353,">="&C5,'IC Live 4004xxxx'!$D$3:$D$353,"<="&EOMONTH(C5,0))

(I changed the lookup range to column D from column B)

 

[Fluff]

As you have time values the last part of the formula should be

"<"&Eomonth(C5,0)+1

(or use Edate) otherwise anything on the last day will not be counted.
If you change the format for the whole of col D to General do you see numbers or dates?

 

[UpToTheGills]

TradingJournal.xlsm
	B	C	D	E	F	G	H	I
3	Month / Year		Gain on A/c				Total No. of trades
4
5	May 2022						0
6	Jun 2022
7	Jul 2022
Stats Overview

Cell Formulas
Range	Formula
H5	H5	=COUNTIFS('IC Live 4004xxxx'!$D$3:$D$353,">="&C5,'IC Live 4004xxxx'!$D$3:$D$353,"<="&EOMONTH(C5,0))

 

[UpToTheGills]

TradingJournal.xlsm
	B	C	D
303	06-Jun 00:27	06-Jun 00:43	05-Jun 22:27
304	03-Jun 00:34	03-Jun 13:25	02-Jun 22:34
305	01-Jun 21:14	01-Jun 23:27	01-Jun 19:14
306	01-Jun 21:06	01-Jun 21:12	01-Jun 19:06
307	01-Jun 20:12	01-Jun 20:22	01-Jun 18:12
308	30-May 23:31	31-May 00:13	30-May 21:31
309	29-May 17:05	29-May 19:21	29-May 15:05
310	28-May 22:00	29-May 15:06	28-May 20:00
311	26-May 20:08	27-May 07:43	26-May 18:08
312	25-May 00:05	25-May 00:44	24-May 22:05
313	24-May 23:22	24-May 23:45	24-May 21:22
314	23-May 21:53	23-May 22:05	23-May 19:53
IC Live 4004xxxx

Cell Formulas
Range	Formula
D303:D314	D303	=IF(B303>0,B303-(1/12),"")

Cells with Data Validation
Cell	Allow	Criteria
D303:D314	Text length	=0