Solved

Question on VBA Coding

Posted on 2014-04-15
16
330 Views
Last Modified: 2014-04-16
Hi All,

I have the below code that is supposed to subtract one month from the AddMonth if the Day of the Month is an 01, but it isn't doing it.  I didn't write this code, so any help would be greatly appreciate...

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + (Right(Hire_2, 2) = "01"), 1)

Open in new window

0
Comment
Question by:Anthony6890
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 57
ID: 40002478
um....it's adding a number of months (AddMonth) to Hire_2 and making it the first day of that month.

So if AddMonth = 2 and Hire_2 is  12/15/13, then the result would be 02/01/14.

Should do what you want if you pass a negative for add month.

I think I would have called it  "MonthOffset" rather then AddMonth.

Jim.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40002487
I get that part, but I need it to work this way. If Hire_2 was 12/1/2013 and the add month was 3, then the result should be 3/1/2013.  But if Hire_2 was 12/2/2013 and the add month was 3, the result should be 4/1/2013...
0
 
LVL 57
ID: 40002493
Change it to:

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + IIF(Right(Hire_2, 2) = "01",-1,0), 1)

 Relying on the check to return -1 as true is not a hot idea.

Jim.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:Anthony6890
ID: 40002497
So what happens with this if the result of fnDenEligible before the check of the day is 01/01- with the -1 bring it back to 12/01 of the previous year?
0
 
LVL 57
ID: 40002521
Yes and you can try it for yourself.  Open a code window, then hit Ctrl/G, then type:

?  DateSerial(Year("13/01/01"), Month("13/01/01") + AddMonth + IIF(Right("13/01/01", 2) = "01",-1,0), 1)

and hit return.   12/01/12 is the result

Jim.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40002607
Ok, great.  I'll try it tomorrow in the office and let you know the results.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40002699
I have a bunch of date functions I created to return certain values.  This one returns the first day of the month for the given date.  So if you add 3 months to 4/15/14, you end up with 7/15/14 which will result in 7/1/14 if passed to this function.
NewDate = FstDayOfMth(DateAdd("m", 3, Hire_2))


Public Function FstDayOfMth(InDate As Date) As Date
    FstDayOfMth = DateSerial(Year(InDate), month(InDate), 1)
End Function

Open in new window

0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40003370
This is just another example why you always must avoid handling dates as strings.
The original code will work, but only for some date formats like yyyy-mm-dd.

So always handle dates as dates. No exceptions.
Here is how:

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + Abs(Day(Hire_2) > 1), 1)

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004380
Jim, you're function didn't work.  Hire_2 is in this format:  mm/dd/yyyy.  For example, someone has the date of 1/1/2013 and the fnDenEligible is displaying 20130501 which is wrong it should be 20130401.

Gustav, I understand what you are saying.  How would you suggest handling this?  

Thank you in advance.  

-Anthony
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40004389
Like I showed, without using Right().

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004399
Gustav, let me ask you a question.  For this person, I had their add Month as 4 because if they were hired 1/2/2013, their fndenelig should be 4/1/2013 but if they were hired 1/1/2013 and fndenelig should be 3/1/2013.  Should the addmonth be 3 now instead?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40004410
Yes, I thought it was 3 - from your example data.

/gustav
0
 
LVL 57
ID: 40004452
<<Jim, you're function didn't work.  Hire_2 is in this format:  mm/dd/yyyy. >>

 Well as gustav pointed out, that's because your working with a date as a string.   The function I posted only works if the day appears last:  YYYYMMDD or YYMMDD

 But certainly mm/dd/yyyy would not fly.

Jim.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40004828
OK, let's try again.  You want to add some number of months to a date.  If that date is the first of the month, no adjustment is necessary.  If that date is any other day, then move the resulting date to the first day of the NEXT month.  So, the function below handles that a little differently.  It adds "AddMonth" if the date is the first of the month or it adds "AddMonth" +1 if it is any other day.  Then the FstDayOfMth() returns the first day of the month for any date passed to it.  So if you pass in the first, you get back the first.  If you pass in the third, you get back the first.

NewDate = FstDayOfMth(DateAdd("m", IIf(Day(Hire_2) = 1,AddMonth, AddMonth +1), Hire_2))


Public Function FstDayOfMth(InDate As Date) As Date
    FstDayOfMth = DateSerial(Year(InDate), month(InDate), 1)
End Function 

Open in new window


You can't add "AddMonth" to the month in isolation as you are doing because that will not handle rolling into a new year correctly.  As has already been pointed out - ALWAYS work with date fields that are defined as dates.  NEVER attempt to do this type of processing on strings or date parts.    You don't need the function I created, you can just use built in functions but to do that, you would still need the DateSerial() function that my wrapper function uses.  The difference would be that you would need to replace the two instances of InDate with the entire expression that includes the DateAdd() and IIf() in the beginning of the example.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004869
Gustav, your fn worked.  I adjusted the addmonth to one month prior and it all works well.  Thank you.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40005276
You are welcome!

/gustav
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question