count occurrenced of today's date in cells where the date is imbedded in other text

Hi All!

I need to count the number of times today's date appears in a specific collumn in another workbook that I have open simultaneously. The date can appear with text either before or after it, for example "online 5/02/2015 SM" or "5/02/2015 AM". I can put today's date in a reference cell on my primary work sheet and use "=COUNTIF(INDIRECT("'[0TV Callsheet Questionaire 2014.xlsm]New Callsheet - Norm'!G8:G1000" ),A1)" where A1 is my reference cell. This will count the number of occurrences of the date but only if the date occurs by itself with no other text in the source cell. I have tried =COUNTIF(INDIRECT("'[0TV Callsheet Questionaire 2014.xlsm]New Callsheet - Norm'!G8:G1000" ),"*"&A1&"*") but this returns a value of zero. How can I get excel to count the dates even when they are imbedded with other text? Thank you for any help. Regards, Itzik

A date is just a number to Excel. Try it like this:

=COUNTIF(INDIRECT("'[0TV Callsheet Questionaire 2014.xlsm]New Callsheet - Norm'!G8:G1000"),"*"&TEXT(A1,"d/mm/yyyy")&"*")

Hope this helps.

Pete

Beautiful! This seems to be working a treat! Thanks so much Pete! I have been playing with this for two days trying to get it to work! Regards, Itzik.

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete