Excel Macro working with dates

July 14, 2010 at 16:06:29
Specs: Windows Vista
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).Select

Range("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"
Next

Thank you for any help.


See More: Excel Macro working with dates

Report •


#1
July 14, 2010 at 17:26:28
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 2011

The 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 next

Regards


Report •

#2
July 15, 2010 at 13:11:36
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!


Report •

#3
July 15, 2010 at 14:21:02
Hi,

You're welcome.

Try this for returning 31 December of the current year:

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

Regards


Report •

Related Solutions

#4
July 15, 2010 at 17:16:45
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!


Report •

#5
July 16, 2010 at 06:32:03
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


Report •

#6
July 19, 2010 at 14:14:24
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


Report •

#7
July 19, 2010 at 14:39:18
You're welcome.

Regards

Humar


Report •


Ask Question