Newbie question using COUNTIFS with text and dates

Hi guys
I'm trying to create a spreadsheet in work using Excel 2010, that will auto update each day, as at the moment I am going in and changing the search criteria, so for today for example I would ask it to search for a cell beginning with 22/07, then go in tomorrow and change it to 23 and so on.

So at the moment let's say in column A i have names of colleagues, and column B, hoping they enter the date first as in dd/mm or dd/mm/yy they will then enter details of what to check like this...


A B
Andrew andy 22/07 - check zzz
Steven stefano 23/07 ok
Andrew zzxxxcc 23/07 check all
Robert roberto 22/07 check 789
Andrew Andy 27/07 check 123
Robert roberto 25/07 check xyz

So my current formula I have

=COUNTIFS(A2:A180,"Andrew andy",B2:B180,"22*")
and other formulas for each person.....

=COUNTIFS(A2:A180,"Steven stefano",B2:B180,"22*")

This tells me how many jobs they have to do on "X" date (providing they don't mess the cells up and miss out the date at the beginning!)

It works OK when there's 4 or 5 people I guess but if you have a team of 20, it can be very time consuming. I also have another column on my results table that tells me anything "<22" so i can see if anything is overdue which again works probably with 95% accuracy providing there's no real old ones like 22/06!

Another way I tried was with today's date serial as per the folowing =COUNTIFS(A2:A180,"Andrew Andy",B2:B180,42938 )

That worked well if there was no other text in the "B" cells, i.e. just 22/07 but it did not pick up if there was text in there with it and unsure if I put +1 after 42938 whether it would automatically increase the day tomorrow?

Anybody have any ideas please? I do realise that for it to work the employees also have to do their bit (The data is automatically being pulled from the system we have that uses Microsoft Dynamics, so they enter the date and details and that is brought over to this spreadsheet I have created, I just need to click on the Data tab and refresh all).

Hope that all makes sense
Much appreciated, thank you

Andy

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

As per Ford's reply: enter dates as dd/mm/yy(yy) and use TODAY() as criterion

=COUNTIFS(A2:A180,"Steven stefano",B2:B180,TODAY())

or better

=COUNTIFS(A2:A180,D2,B2:B180,TODAY())

where D is list of staff names

Thanks guys, I have attached an example spreadsheet to give you an idea, also you will see (hopefully) in bold the one where the agent didn't put the date at the beginning so that doesn't get counted
In column B you will see my formula is looking for the cell that begins with 23 as in today's date, and column C is anything less than today's date.
It's not too bad in this example as you only have a few workers, but can become a pain if you have 30+ on a team and having to change each one to 23, then 24 etc.
Thanks again
Andy

Thanks John
I tried what you said, however it doesn't work with the text in there as well unfortunately. To test I formatted the cell to both date then text and tested both. The minute I removed the text and left 23/07/17 in there it worked but we need the text as well so the workers can see at a glance what needs to be done i.e. "23/07/17 call Pete". Also if that person is off, someone else can see what needs to be done and pick it up for them, without going into all the notes

You have broken one of the cardinal rules of Database design: do not use a field (column) for multiple purposes. The date and associated text should in separate columns.

See attached. Formulae as per my previous post.

... update .

change F6

=COUNTIF($A$2:$A$180,E6)

Ah my bad! Thanks John. Unfortunately though I'm not sure if the Microsoft Dynamics based system we use would be able to split the data. That is, we have a system that holds customers records and those that are dealing with it will add into the case name field, the next contact date and what actions to be taken as in "23/07/17 call Pete". That gets imported into the Excel worksheet as just "Case Name" field. I am guessing Excel would need to be a magician to then automatically separate the date and text into two columns like what you have done? But at least we are getting somewhere so if we can work without adding comments I guess, it would work and update every day. Not sure if the bosses will have that but it is still a step in the right direction so thanks again!

It would be possible to separate out text and date: I think this is something you should consider doing.

IF All data is of the form of your file;

in C2

=IF(B2<>"",IF(ISNUMBER(LEFT(B2,5)+0),LEFT(B2,5)+0,RIGHT(B2,5)+0),"")

in D2

=IF(B2<>"",IF(ISNUMBER(LEFT($B2,5)+0),MID($B2,FIND(" ",$B2,1)+1,255),LEFT($B2,LEN(B2)-6)),"")

Formulae in table adjusted for new ranges.

Wow! Thanks John will give that a go on the file in work, thanks for your help and your time much appreciated. I'm going to mark this as resolved, thanks for all your help