Calculating End of Month 23 months from today

[kevinsf]

Full disclosure, I know nothing about coding.

My company has label software that requires an expiration date for our product. Currently, we manually input this date. It is an issue because if the date is incorrect it would require us to perform a recall.

Is there a way to create a code that automatically calculates the expiration date that would be end of month in intervals of 5, 11, and 23 months?

[PhilOfWalton]

Recalls sound expensive.

It is not difficult to do, but how do you intend to input the information and get the output.

Sorry, you need code, so you need to create a module with this code:-

Option Compare Database
Option Explicit
 
Function LastDayOfMonth(StartDate As Date, MonthsAway As Integer) As Date
'Print LastDayOfMonth(#1/2/17#, 2)   January second
'Print LastDayOfMonth(#2 jan 2017#, 2)   January second
'###### NOTE DATE MUST BE ENTERED AS #mm/dd/yyyy# or #dd mmm yyyy#
 
    Dim NewDate As Date
 
    NewDate = DateAdd("m", MonthsAway, StartDate)
 
    LastDayOfMonth = DateSerial(Year(NewDate), Month(NewDate) + 1, 0)
 
End Function
 

Note the remarks that the start date must me in American format or with the month spelt out.

Phil

[kevinsf]

Thank you Phil. I perhaps should have included the need for the format to be YYYY-MM-DD. What would I need to change in order to get that format?

[PhilOfWalton]

Is that the Input or Output format or both?

Phil

[SioSio]

Phil's code is correct, but shows another way.

Dim baseDay As New DateTime(2000, 8, 31, 20, 30, 0)
Dim date1 As DateTime    = baseDay.AddMonths(23)
Dim days As Integer = date1.DaysInMonth(date1.Year,date1.Month)
Dim lastDayOfDate1 = New DateTime(date1.Year, date1.Month, days)