Derive first and last date in vb

[jabmod]

Using variables Startdt, Enddt, how do I determine the first day and last day of a given month.

For example if Date1 = 12/24/04
Without hard coding the days, how do I make
startdt - 12/01/04 and
Enddt - 12/31/04.

Thanks.

 

[SonOfEmidec1100]

I'm sure theres more than 1 way. This will work for different short date formats dd/mm/yy or mm/dd/yy

startdt = CDate("01-" & Format(Date1, "mmm-yyyy"))
enddt = CDate("01-" & Format(startdt + 31, "mmm-yyyy")) - 1

 

[Zathras]

Here is one way (in-line code):

Sub test()
Dim Date1 As Date
Dim startdt As Date
Dim Enddt As Date
  Date1 = "12/24/04"
  startdt = DateSerial(Year(Date1), Month(Date1), 1)
  Enddt = DateSerial(Year(startdt + 45), _
                     Month(startdt + 45), 1) - 1
  MsgBox startdt & "  " & Enddt
End Sub

Here is my preferred way (re-usable functions):

Sub test2()
  MsgBox MonthBegin("12/24/04")
  MsgBox MonthEnd("12/24/04")
End Sub

Function MonthBegin(ADate As Date) As Date
  MonthBegin = DateSerial(Year(ADate), Month(ADate), 1)
End Function

Function MonthEnd(ADate As Date) As Date
  MonthEnd = DateSerial(Year(MonthBegin(ADate) + 45), _
                   Month(MonthBegin(ADate) + 45), 1) - 1
End Function

 

[PHV]

startdt - DateSerial(Year(date1), Month(date1), 1)
Enddt - DateSerial(Year(date1), Month(date1)+1, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi jabmod,

I'm with Zathras that re-usable functions are best, but a couple of points.

The MonthEnd date can be more simply got by using ..

[blue]MonthEnd = DateSerial(Year(ADate), Month(ADate) + 1, 0)[/blue]

But, better again in my book, is to use the EOMONTH Function. You need the Analysis ToolPak VBA AddIn installed and referenced for this, but you can then use ..

[blue]EoMonth(ADate, 0)[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jabmod]

Thank you all...