transform serial numbers (yyyymm) to dates dd/mm/yy

[Hermac]

Hello, I'm looking for the most efficent VBA code to transform the 6 digit numbers in the first row to the dates in the second row, with dd as the last day of the month.

202301 202302 202303 202304 202305
31/01/23 28/02/23 31/03/23 30/04/23 31/05/23
Can anyone help me ?
Thanks a lot!
Herman

 

[RoryA]

I assume you want code specifically? You could use something like:

Function EndOfMonth(inp As Long)
   EndOfMonth = CDate(Format(CLng(inp + 1 & "01"), "0000-00-00")) - 1
End Function

 

[Hermac]

I assume you want code specifically? You could use something like:

Function EndOfMonth(inp As Long)
   EndOfMonth = CDate(Format(CLng(inp + 1 & "01"), "0000-00-00")) - 1
End Function

Thanks Rory! that is really efficient and works like a charm but there is a flaw at the last day of each year.
Like this : ("Waarde" is Dutch for "Value")

Is there any solution?
Thanks again.
PS I love Labradors!
Herman

 

[Phuoc]

Try this code:

Function EndOfMonth(num As Long)
   EndOfMonth = DateSerial(Left(num, 4), Right(num, 2) + 1, 0)
End Function

 

[Phuoc]

Or:

EndOfMonth = DateSerial(num \ 100, (num Mod 100) + 1, 0)

 

[Hermac]

Try this code:

Function EndOfMonth(num As Long)
   EndOfMonth = DateSerial(Left(num, 4), Right(num, 2) + 1, 0)
End Function

The perfect solution. Thanks