Fix Date Issue then place in macro

[spacle]

Hi! I have a report that has 4 columns with dates (not each cell has a date). I need to tally the number of scheduled calls and completed calls by manager. I would like to include this in my macro that does a bunch of other stuff to update the report. The dates come out of our system in yyyymmdd format, and I can use text to columns to change them to mm/dd/yyyy format.

In my limited knowledge, I feel like I need to use a concatentate to get the dates all in one column, then have a formula that will identify dates that have already occured versus dates in the future, then filter by manager with a filter that will tally the number of dates in the past and the number of dates in the future (completed versus scheduled calls), then copy and paste the totals onto the main report. This seems a little unwieldly and I am betting there is an easier way to do it. Keeping in mind that all this needs to be performed by the macro without anything being done by the user other than running the macro.

Any suggestions?

Thanks in advance for your help!

[xladept]

Can you post a sample? (Go to advanced and click on the paper clip.)

[spacle]

Here is the example. I actually use Excel 2007, but that is in our network, whereas to attach this file, I had to go outside of our network so it may have reverted back to a previous version of Excel, but 2007 is what I am using.

Thanks for looking!

[xladept]

Hi Spacle,

Here's some code for a tally - I output it to sheet2:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Save your Workbook or Another Workbook designated for Macros as Macro_Enabled

Press ALT + F11 to open the Visual Basic Editor.

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[spacle]

Thank you so much for your help! That didn't do everything I needed, but it significantly reduced the steps I had to take to get the data I needed. I combined the macro you wrote with a formula to see which dates were past versus future (I used =IF(TODAY()>C2,"Completed","Scheduled") and then used a pivot table to get each persons number of completed and scheduled calls.

Thanks again for everything!

[xladept]

You're welcome!