Microsoft Excel 2007

I am trying to figure out a way to get a frequency count of multiple fields that contain dates. I have 10 columns that contain dates and need to figure out how to get a count of the dates in each column. Any ideas?

Could you be a little more specific? Are you looking for a count of just dates in general or are you looking to count specific dates, etc.

Maybe some examples of your data and the expected result would help.

Thanks!

Hi, Use the countif() function.

COUNTIF(range,criteria)

The range is the range of cells with your dates.

Criteria is a date you want to countEnter a date in cell A1

in Cell A2 enter =A1+1

Drag the formula in A2 down to create a sequential list of dates.In cell B1 enter countif(Range, A1)

where Range is the range of cells with dates to be counted.

Make sure the range uses $ signs e.g. $C$1:$AB$500 so that the range will remain the same when you drag the countif() formula.Now drag the formula in B1, down alongside your column of dates.

The count of each date in your range should appear.

If dates have not been entered as proper Excel dates you may have an issue, or if the dates have some time information in them.

When Excel recognizes an entry as a date it converts it to a number, and formats it as a date.

Go to a cell with a date in it and then try formating the cell with different date formats. If the cells change when you change the format, Excel recognized them as dates. If there is no change, then Excel has treated the entry as text only.

If text only, re enter the dates as 09-Oct-2009 for example.

Format a date cell as a number with decimal places, and if the decimal part is not zero then there is time information stored with the dates, and some data repair work will be required! (If so ask for more advice).

Regards

Thank you! It worked beautifully!

You're welcome.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History