Date difficulty

[Robert E Lee]

Hi all

I have extracted the date from a file name using the following code

Date1 = Right(Left(Name, Len(Name) - 4), 9)

I now need to use this text date to create a date in DD/MM/YYYY format so using Date1 i tried this

Date2 = Left(Date1, 3) & "/" & Right(Date1, 6)

which failed

doing the same thing in two stages and then concatenating the results did produce the desired result

Month = Left(Date1, 3)
Year = Right(Date1, 6)
Date2 = Month & "/" & Year

I would love to know why the second is acceptable but not the first

Also, if I may add a second question, when the "Date2" is entered the date shows as 01/09/2109, is there any way I can make it 30/09/2019 and to have the last day of succeding months

Many thanks

Rob

 

[Special-K99]

Date1 = Right(Left(Name, Len(Name) - 4), 9)

Why are you using nine characters? The usual standard is 6 or 8 or possibly 10, why 9?
What's your input value for Name ?

Last day of month
https://www.acctadv.com/need-to-determine-the-last-day-of-a-month-in-vba/

 

[dmt32]

Hi,
you have not shown what your date string looks like but if it is a string date, then try using the DateValue Function

Date1 = DateValue(Right(Left(Name, Len(Name) - 4), 9))

This should change the string in to a Date & the function recognizes the order for month, day, and year according to the Short Date format that you specified for your system


Dave

 

[Robert E Lee]

Thank you both for your comments. I am creating a .csv for import to Sage and this requires that there be a text date in one field and a date format date in another. The Date1 works perfectly even though from what has been said it shouldn't and this is used for the text date.

The second part of my question was asking why it should be that the first set of code for the date format date doesn't work and the second does.

Finally when the non-text date is entered it reads as 01/09/2019; I would like to be able to change this to 30/09/2019 or to whatever day is the last of the month in question

Thanks
Rob

 

[footoo]

Month = Left(Date1, 3)
Year = Right(Date1, 6)
Date2 = Month & "/" & Year

I presume that is not your actual code because you can't use Month and Year as names of variables.

You did not post what Name looks like (as requested by Special-K99 and dmt32).
I'm guessing it looks like this : 0mm00yyyy####

If so, the reason why your first code doesn't work is because it produces a string like : 0mm/00yyyy
With the second code, presumably the variables are defined as numbers so produce : mm/yyyy

Try this instead (also produces the last day of the month) :

Dim name$, date2 As Date
name = "011002019####"
date2 = WorksheetFunction.EoMonth(DateSerial(Mid(name, 6, 4), Left(name, 3), 1), 0)

 

[footoo]

I presume that is not your actual code because you can't use Month and Year as names of variables.

Forget that - I think you can.

 

[Robert E Lee]

Hi

I apologise for not posting the filename which is Dev CB Sep 2019 and also confess that dmt32 was correct in that my "9" produces an incorrect result having a spurious . at the end.

So I got that bit sorted and would like to ask footoo how to use the worksheet function using the result produced by my first line of code.

Thanks

Rob

 

[footoo]

Dim name$, date2 As Date
name = "Dev CB Sep 2019.xlsx"
date2 = WorksheetFunction.EoMonth(DateValue("1/" & Mid(name, 8, 3) & "/" & Mid(name, 12, 4)), 0)

 

[Robert E Lee]

Thanks footoo for your help that has done the trick

Regards

Rob

 

[FormR]

Hi, you could also try setting the date2 variable using this line:

date2 = DateAdd("m", 1, Mid(name, 8, 8)) - 1