By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,384 Members | 671 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,384 IT Pros & Developers. It's quick & easy.

Calculating End of Month 23 months from today

P: 2
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
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
  3.  
  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#
  8.  
  9.     Dim NewDate As Date
  10.  
  11.     NewDate = DateAdd("m", MonthsAway, StartDate)
  12.  
  13.     LastDayOfMonth = DateSerial(Year(NewDate), Month(NewDate) + 1, 0)
  14.  
  15. End Function
  16.  
Note the remarks that the start date must me in American format or with the month spelt out.

Phil
Jul 28 '17 #2

P: 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

PhilOfWalton
Expert 100+
P: 1,430
Is that the Input or Output format or both?

Phil
Jul 28 '17 #4

Post your reply

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