 # VBA code to calculate number of dates in column that are after todays date

• Hi

I need a VBA formula that looks at a column of a large number of dates and calculates the number of dates that are >= todays date.

I have the excel formula

Code
1. =countif(L:L,">="&today())

But how is that expressed in VBA?

I also need seperate VBA code to count the dates between TODAY and +90 days time.

All help appreciated.

Thanks

• Re: VBA code to calculate number of dates in column that are after todays date

OK

I think I got the countif VBA to work.

Code
1. Dim x As Long
2. Dim z As Date
3. z = Date
4. With Sheets("SOPs")
5. x = Application.WorksheetFunction.CountIf(.Columns("L:L"), ">= & z")
6. End With
7. Worksheets("MTD Stats").Range("C19").Value = x

Appears to work fine.

• Re: VBA code to calculate number of dates in column that are after todays date

Hello experts

Please can you help with my coding.

I have tested it but it doesn't count properly.

The first part works but the counting for 'prior to TODAYS date' and 'next 90 days from TODAYS date' doesn't.

Regards
Dejamls

• Re: VBA code to calculate number of dates in column that are after todays date

Change your formula where you reference "<= & z") to "<=" & z).

• Re: VBA code to calculate number of dates in column that are after todays date

Hi

Still not working.

The two excel formulas I need converted to VBA if anyone can help, much appreciatted.

They work but I need them in VBA.

=COUNTIF(SOPs!L:L, "<=" & TODAY())

=COUNTIFS(SOPs!L:L,"<="&TODAY()+90,SOPs!L:L,">="&TODAY())

• Re: VBA code to calculate number of dates in column that are after todays date

Solution I finally got was inputing formula into cell that I wanted result in and then - clicked record macro - clicked cell - clicked F2 - hit enter - stopped recording - copied recorded VBA into code.

Works!

If there is a better way then glad to hear.