Dates in excell

[Guest]

The statement MyDate = (Mid$(fname, e, 8)) returns 8 characters from a
string in the format

JUL-2006

Excel is adding to that the day of the month so its returning the date
1/july/2006
I want it to default to the last day of the month and not the first, Is
there an easy way of doing it?

Thanks in advance.

Mike

 

[Nigel]

One solution, this extracts the date string, adds 1 month and then subtracts
1 day.
Error arises if the string is not a valid date!

MyDate = Dateadd("d",-1,Dateadd("m",1,Mid$(fname, e, 8)))

 

[Norman Jones]

Hi Mike,

Try something like:

'=============>>
Public Sub Tester()
Dim myDate As Date
Dim fname As String
fname = "JUL-2006"

myDate = DateValue(fname)
myDate = DateSerial(Year(myDate), Month(myDate) + 1, 0)
Debug.Print myDate
End Sub
'<<=============