CODEWITHSUNDEEP
vbaexcel
Kim
Kim

Reputation: 163

Formatting Variable as Date (mm/dd)

I am trying to create a script that identifies the date range of the previous month (Cell G9 is today's date). It is working; however, the output is formatting the date much differently than I am intending. The [J36] output is "01/08" when it should be "08/01" and the [J37] output is "6:11:37 AM" (interpretting it as a time), when it should be "08/31". I am only coding for the output in particular cells because I want to see that it is working. However, it is the variable I want to format, not the cell. I'm going to be using the startdate and enddate variable in future code and those variables on their own won't be visable in any cells.

I attempted to use the format(startdate,mm/dd), but I got an overflow error.

Here is my script:

Dim thismonth As Integer
    thismonth = Month(Sheet1.[G9])
Dim startdate As Date
Dim enddate As Date


Select Case thismonth
    Case 1
        startdate = 12 / 1
        enddate = 12 / 31
    Case 2
        startdate = 1 / 1
        enddate = 1 / 31
    Case 3
        startdate = 2 / 1
        enddate = 2 / 28
    Case 4
        startdate = 3 / 1
        enddate = 3 / 31
    Case 5
        startdate = 4 / 1
        enddate = 4 / 30
    Case 6
        startdate = 5 / 1
        enddate = 5 / 31
    Case 7
        startdate = 6 / 1
        enddate = 6 / 30
    Case 8
        startdate = 7 / 1
        enddate = 7 / 31
    Case 9
        startdate = 8 / 1
        enddate = 8 / 31
    Case 10
        startdate = 9 / 1
        enddate = 9 / 31
    Case 11
        startdate = 10 / 1
        enddate = 10 / 31
    Case 12
        startdate = 11 / 1
        enddate = 11 / 30
End Select

Sheet1.[J36] = startdate
Sheet1.[J37] = enddate

Upvotes: 0

Views: 126

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

How about a much simpler solution:

Dim startDate as Date
Dim endDate as Date

startDate = Application.WorksheetFunction.EOMONTH(Sheet1.[G9],-2) + 1
endDate = Application.WorksheetFunction.EOMONTH(Sheet1.[G9],-1)

You can get rid of VBA altogether and write this worksheet formula directly in J36 and J37 respectively.

J36: =EOMONTH(G9,-2)+1

J37: =EOMONTH(G9,-1)

Upvotes: 4

Related Questions