470,590 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,590 developers. It's quick & easy.

Calculating End of Month 23 months from today

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?
Jul 28 '17 #1
4 3086
1,430 Expert 1GB
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:-

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Function LastDayOfMonth(StartDate As Date, MonthsAway As Integer) As Date
  5. 'Print LastDayOfMonth(#1/2/17#, 2)   January second
  6. 'Print LastDayOfMonth(#2 jan 2017#, 2)   January second
  7. '###### NOTE DATE MUST BE ENTERED AS #mm/dd/yyyy# or #dd mmm yyyy#
  9.     Dim NewDate As Date
  11.     NewDate = DateAdd("m", MonthsAway, StartDate)
  13.     LastDayOfMonth = DateSerial(Year(NewDate), Month(NewDate) + 1, 0)
  15. End Function
Note the remarks that the start date must me in American format or with the month spelt out.

Jul 28 '17 #2
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?
Jul 28 '17 #3
1,430 Expert 1GB
Is that the Input or Output format or both?

Jul 28 '17 #4
269 256MB
Phil's code is correct, but shows another way.

Expand|Select|Wrap|Line Numbers
  1. Dim baseDay As New DateTime(2000, 8, 31, 20, 30, 0)
  2. Dim date1 As DateTime    = baseDay.AddMonths(23)
  3. Dim days As Integer = date1.DaysInMonth(date1.Year,date1.Month)
  4. Dim lastDayOfDate1 = New DateTime(date1.Year, date1.Month, days)
Dec 17 '19 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by bufbec | last post: by
8 posts views Thread by John | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.