Hello,

I am trying to write a macro to determine the percentage of dates in a column of cells (format: 30-Sep-10) that occur before the end of the current calendar year.

I have tried using the DCOUNTIF function, but I think I am having problems with excel recognizing the date and working with the date in the context of the current calendar year. This is what I have, which doesn't seem to work at all. The C[-2] is the column with the dates in it:For i = 2 To Sheets.Count

Worksheets(i).SelectRange("J8").Select

ActiveCell.FormulaR1C1 = "=DCOUNTIF(C[-2]:C[-2],<DATE(year(now())+1,0)"

Range("K8").Select

ActiveCell.FormulaR1C1 = "=DCOUNT(C[-3]:C[-3])"

Range("L8").Select

ActiveCell.FormulaR1C1 = "=((RC[-1]-RC[-2])/RC[-1])"Range("J6").Select

ActiveCell.FormulaR1C1 = "Ground Task IDs"

Range("J7").Select

ActiveCell.FormulaR1C1 = "due this year"

Range("K7").Select

ActiveCell.FormulaR1C1 = "Req"

Range("L7").Select

ActiveCell.FormulaR1C1 = "% complete"

NextThank you for any help.

Hi, I don't think that you need a macro to do this.

As a test I had 1000 dates in column A in cells A2 to A1001

The dates ranged from 01 January 2009 to 31 December 2011The method I used is based on counting the total number of dates and the total number of dates that are on or before 31 December 2010.

Then calculate the percentage.

=COUNT(A2:A1001) counts all cells containing numbers (Excel stores dates as numbers - 40373 is 14 July 2010)

I used =COUNTIF(A2:A1001,"<=" & DATE(2010,12,31)) to count the number of cells containing a date on or before the 31st December this year.Putting all this into one formula I get:

=COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))/COUNT(A2:A1001)

Then format the cell with a percentage number format.

Or if you prefer the value directly use:

=(COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))*100)/COUNT(A2:A1001)Regarding a macro, i did this:

Option Explicit Sub datePercnt() Dim intTotal As Integer Dim sngBefore As Single Dim rngDates As Range Dim dtEndDate As Date Dim rngCell As Range Dim sngResult As Single 'set the range containing dates Set rngDates = Worksheets("Sheet1").Range("A2:A1001") 'set counters to zero intTotal = 0 sngBefore = 0 'set end date dtEndDate = DateSerial(2010, 12, 31) 'loop through each cell in the range For Each rngCell In rngDates 'add 1 to count of all dates intTotal = intTotal + 1 'test date in cell against end date If rngCell.Value <= dtEndDate Then 'if on or before add 1 to 'before total' sngBefore = sngBefore + 1 End If Next rngCell 'calculate percentage sngResult = sngBefore * 100 / intTotal 'display result MsgBox "The percentage of dates in the range:" & vbCrLf _ & CStr(rngDates.Resize(1, 1).Address) & " to " _ & CStr(rngDates.Offset(rngDates.Rows.Count - 1, rngDates.Columns.Count - 1) _ .Resize(1, 1).Address) _ & vbCrLf & "that were on or before: " & Format(dtEndDate, "dd-mmm-yyyy") _ & vbCrLf & " was: " & Format(CStr(sngResult), "#0.00") & "%" End Sub

Notes

1. In Visual Basic macros it is typically not necessary to select a cell before doing something with it.

2. Looping through a range of cells (or a range of objects) can be done with the for each ... next structure. Excel takes care of the counting. You can loop through a collection of worksheets in a workbook, without having to know how many there are - Excel keeps track for you.

3. As dates are stored as numbers, most times you can do comparisons, differences etc with simple math functions (but remember that Excel also stores times in the same number (as the decimal part) so sometimes 31 December 2010 is not equal to 31 December 2010 if the times are different. To be sure, you can use the integer or whole number part of the values so that just the date is compared.

4. I try and use the A1 notation for cells as I find it easier to check what I have against the actual worksheet.

5. Use Option explicit - it helps reduce errors due to typos. It shouldn't let you do sngBefore = sgnBefore +1 and you must DIM everything before you first use it - that also helps with Intellisense that prompts you with what is available, such as typing rngCell then the dot and you get a list of what can come nextRegards

Humar,

Thank you for your help; the macro worked well; now I can integrate it into another macro which calculates this on multiple worksheets.Is there a way to make this macro automatically select the end of the current calendar year rather than defining a specific year?

dtEndDate = DateSerial(2010, 12, 31)

I would like the macro to work in the years to come without having to edit the macro each year.Thanks very much for your assistance Humar!

Hi, You're welcome.

Try this for returning 31 December of the current year:

dtEndDate = DateSerial(Year(Now), 12, 31)Regards

Humar,

I get "FALSE" (with no formula present)in both of the cells that I apply these formulas:Range("J8").Select

ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2]:C[-2], " <= " & DateSerial(Year(Now), 12, 31))"-and-

Range("M8").Select

ActiveCell.FormulaR1C1 = "=(COUNTIF(H2:A200," <= " & DATE(2010,12,31))*100)/COUNT(H2:A200)"Can you assist?

Thank you!

Hi, You are using a Visual Basic macro to place formulas into cells in a worksheet.

The formulas you put into cells must use the worksheet syntax.

The code I gave you:dtEndDate = DateSerial(Year(Now), 12, 31)

is Visual Basic syntax.In a worksheet cell you would use standard worksheet syntax:

=Date(Year(Now()),12,31)Also in both examples you have an extra space:

" <= " instead of "<= "The reason you get FALSE is that Visual Basic is evaluating this line:

"=(COUNTIF(H2:A200," <= " & DATE(2010,12,31))*100)/COUNT(H2:A200)"Because of the double quotes, it is comparing this text:

"=(COUNTIF(H2:A200,"

(using <=) with this text:

" & DATE(2010,12,31))*100)/COUNT(H2:A200)"

Visual Basic treated the double quotes in your formula as the start and end of pieces of text.

The <= fell between quoted text and was treated as an operator.As the the first piece of text was not equal to or less than the second piece of text, the comparison returned FALSE,

which is what was placed in the cell.To do this you have to create the formula without using any characters that Visual Basic would interpret as operators or 'control codes' so " and & are not allowed and have to be replaced by their equivalent character codes:

Range("M9").Formula = "=(COUNTIF(H2:A200," & Chr(34) & "<=" & Chr(34) & Chr(38) & "DATE(2010,12,31))*100)/COUNT(H2:A200)"Note that you do not have to select a cell to act on it.

Also you are mixing .Formula and .FormulaR1C1

In your second example you are telling Excel to expect a formula that uses the R1C1 notation, but you are passing it an A1 notation formula. Excel tries to change H2:A200 into an R1C1 notation and I think that it assumes that H2 is a worksheet name (not sure on this - but you certainly don't get the result you expected).... and just an observation: I am wondering why you are using Visual Basic code to write formulas.

If your code did some calculation to create the formulas that would make sense, but here all the formulas are already hard-coded, so it's no different to entering the formulas directly into the cells themselves.

What you have seems to be complicated.Regards

Humar,

Thanks very much. It is working now. This macro is an ever-developing one that I am trying to make more useful as I get better at visual basic. I appreciate your help and instruction.Best Regards,

dataman

You're welcome. Regards

Humar

Ask Your Question

Weekly Poll

Do you believe Hulu's new ad-free service is worth the extra cost?

Discuss in The Lounge

Poll History