Hi folks,
I'm sure there is a very simple solution to this issue.
See the attached sample spreadsheet.
In cell F3 I am attempting to retrieve a count of cells that have todays date. Trouble is the date field is actually date & time. I do not want to seperate the date from the time into two columns.
How do i modify the query to return the correct number?
Hi
a possible approach
Please Login or Register to view this content.
Another formula could be:
RegardsPlease Login or Register to view this content.
Last edited by canapone; 03-23-2017 at 11:15 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Try
=SUMPRODUCT(--(INT(E5:E21)=A1))
Try this ...
=COUNTIFS(E:E,">="&A1,E:E,"<"&A1+1)
Two replies while I was away making a pot of tea... My effort was the same as John's... so I've removed mine!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I count three rows where todays date appears. Neither of those formulas work guys. :-(
Sorry my bad, the dates in the rows are a month out. Tested Johns and it works. Thank you.
My next question is how would I incorporate times as well....Say 23/02/2017, before 09:00 ?
Hi,
Please Login or Register to view this content.
Last edited by canapone; 03-23-2017 at 11:31 AM.
=COUNTIFS(E:E,">="&A1,E:E,"<"&A1+TiME(9,0,0))
... or
=SUMPRODUCT(--(INT(E5:E21)=$A$1)*(MOD(E5:E21,1)<=9/24))
Yes I have noticed this. John's formula works. All I need now is how to add the time value, so say pre 09:00Originally Posted by canapone
See posts 9, 10 & 11
Try
=SUMPRODUCT((INT(E5:E21)=A1)*(MOD(E5:E21,1)<9/24))
....wasn't even making a cup of tea!!
WOWSERS guys, you are all legends. Thank you so much. Glenn - your formula works like magic.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
One last thing !
How would I incorporate a further criteria from a differernt column. So for example, 23/02/2017, pre 9am and is COMPLETED.
Try
=SUMPRODUCT(--(INT(E5:E21)=$A$1)*(MOD(E5:E21,1)<=9/24)*(I5:I21="COMPLETED"))
Nailed it ! thanks. CLosing thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks