how to convert yyyymm number string into end month

Question

Currently, in my Excel, there is a string of year and month written as yyyymm. Eg: 201706

Right now I would like to convert it into the end of month date in another worksheet. Eg: 20170630

The question now is, how do I tell Excel to auto create the day based on the month value in the string?

I was planning on using if statements to declare each month based on the ending value at the back of the string. (ie: If 201706 , date = 20170630)

But then I thought that won't work for February's leap year. Is there another method to use besides that?

Answer 1

Dim dLast As Date
 dLast = DateAdd("d", -1, CDate("1/" & Format(Date, "mm/yyyy")))

Or through a messageBox if you put the date in cell A1:

MsgBox Format([eomonth(A1,6)], "dd/mm/yyyy")

Answer 2

The easiest way to solve your problem is to not even use a vba makro. Using the Date, Leftand Right as well as EOMonth functions you can sinply convert your string to a date like in the example below.

Using this you don't even need vba:

=EOMONTH(DATE(LEFT(A1;4);RIGHT(A1;2);1);0) (example if date string is in A1)

Check out this page to get more detail on this function

Answer 3

I often use a little workaround by taking the first day of the following month (which is always the 1st) and subtract 1 of the date. Unfortunately that only works with Date-Values:

Try this one:

Sub LastDayOfMonth()

    strInput = "201711"
    y = Left(strInput, 4)
    m = Right(strInput, 2)

    dat = DateSerial(y, m + 1, 1) - 1

End Sub

Answer 4

Try this

dtmDate = 201706 (or put there value from cell)

dhLastDayInMonth = DateSerial(Left(dtmDate, 4), Right(dtmDate, 2) + 1, 0)