COUNTIFS dates match, dates as numeric but in text cell with many other numbers

Lanz

New Member
Joined
Jun 9, 2010
Messages
5
Hi, again thanks for previous help, I would not ask had I not tried several times already, but my new question is this:

I have a column listed as the ID tag for a dataset. This column has a large number for every row, with numbers representing different values of the identification, and formated as text (by default because import procedures require this to get true values).
e.g. 1023419990516004
-Where 10234 is the agent #, 19990516 is the date (year, month, date), and 004 is the survey # for that day for that agent.

I have made previous template keeping the stats locked and can drag and drop datasets to get different statistics without manipulating columns. My hope is to extract the date, or rather use COUNTIFS the cells meet a date range (season) without creating a new column that extracts these values.
e.g. =countifs(sheet!range:range,">=....19990320...,sheet!range:range,"<=....19990620...,sheet!rang:range,(other criteria)).

Anybody have an idea on how to do this or if this is possible without formatting column as number? If not, what would I place in the format code to extract this date? Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
Let's say that your value is in cell F3. You can use this formula to extract the date:

=DATE(MID(F3,6,4),MID(F3,10,2),MID(F3,12,2))
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Would something like this work?
Excel Workbook
ABCD
1IDStart DateEnd DateCount
210234199905160005/17/19995/19/1999
31023419990517000
41023419990518000
51023419990519000
61023419990520000
Sheet
 

Lanz

New Member
Joined
Jun 9, 2010
Messages
5
Thank you both for the quick reply. Joe thanks for the code. AlphaFrog's function looks as if it will allow additional columns in the stats sheet, which is fine so long as none are added to dataset, and just what I needed. I will try it and let you know how it worked..
Thank you so much.
 
Last edited:

Forum statistics

Threads
1,081,727
Messages
5,360,910
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top