Dates in excell
 

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

Dates in excell
 

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)))

--
Cheers
Nigel



"Mike" wrote in message
...
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

Dates in excell
 

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
'<<=============


---
Regards,
Norman



"Mike" wrote in message
...
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