Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.I received a tweet last week from @rcdl:
How do you count how many date/time values match a given date?
It was a Thursday night and I was out having dinner with a bunch of friends. But I felt compelled to answer the call. SO I did.
First, we must remember that dates/times in Excel are stored as numbers, and the unit of those numbers is days. So any time within a day is simply that day's number, with a decimal extension to represent the amount of the day that has elapsed.
Let's assume we've got a column of date/time stamps running down column A. And We have a test-case date in cell D1. Out goal is to figure out how many of the entries in column A fall on the day in question.
Our first task is to round the date/times down, essentially getting rid of the time of day at which the events occurred. We do this using the following formula in cell B1:
=ROUNDDOWN(A1,0)
This rounds A1 down to zero decimal places. Using the ROUND function would round all times after midday up, to the following day. Which is not what we want.
That formula can then be copied down column B as far as is necessary.
Next, we need to count how many of the entries in column B match the entry in D1. So in E1, we use the following formula:
=COUNTIF($B:$B,D1)
That simply counts the number of entries in column B that are identical to D1.
Job done.
