CODEWITHSUNDEEP
vbadateexcel
prabhakaran
prabhakaran

Reputation: 5274

How to get the dates of a particular month

I am working in a excel macro. In that I need to generate all the dates belong to a particular month. Can anybody say how to achieve that?

UPDATE: I want to receive it as an array of numbers. I tried to use Date related function which were under namespace "System". But, it is showing error as "object or method not found". So, I am using Issan's idea(see below). But, I am getting error as "Object doesn't support this property or method".

EDIT: The "Object doesn't support this property or method" error went out after I declared a variable as Date. I think the available objects in VBA are depend on declared variables. Thank you to all of you.

Upvotes: 1

Views: 6551

Answers (2)

Gaijinhunter
Gaijinhunter

Reputation: 14685

(Assuming you meant VBA since you say you are 'working in an Excel macro')

Here's how you can generate an array of Dates for each day in a particular month (there's not enough info to suggest what format you want so I am assuming an array of dates).

Sub GenerateDates()

Dim Days() As Date
Dim DaysInMonth As Long, i As Long
Dim Year As Long, Month As Long

' Example: Feb, 2006 (leap year)
Year = 1996
Month = 2

DaysInMonth = DateSerial(Year, Month + 1, 1) - _
              DateSerial(Year, Month, 1)

ReDim Days(1 To DaysInMonth)
For i = 1 To DaysInMonth
    Days(i) = DateSerial(Year, Month, i)
Next

'Do as you wish with the array of 29 days

End Sub

How it works: You can get the number of days in a month using the dateserial math above. This will ensure that you get the proper amount of days, even considering leap years. Then you just redim your array of days and populate the array!

Upvotes: 4

JMax
JMax

Reputation: 26591

If you want to use VBA, then Issun's answer is the best way to handle this.

Yet, this is probably part of a bigger issue and from your question, we can't see what you want to achieve.

Don't forget that you can also use the Excel Spreadsheet to help you building a whole application.

For instance, you could use the formula =EOMONTH(NOW(),0) to get the last day of the current month. Or adapt it the way you want on any other date.
Note : don't forget to check the Analysis ToolPak Add-in so that the EOMONTH formula will exist.

[EDIT] You can find some more info about EOMONTH on this blog's article

Upvotes: 1

Related Questions