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

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" <(E-Mail Removed)> wrote in message
news:0225AA4D-743F-478A-B0DD-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:0225AA4D-743F-478A-B0DD-(E-Mail Removed)...
> 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