Question on VBA Coding

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

LVL 1
Anthony6890Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Anthony6890Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Anthony6890Author Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Anthony6890Author Commented:
Ok, great.  I'll try it tomorrow in the office and let you know the results.
0
 
PatHartmanCommented:
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
 
Anthony6890Author Commented:
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
 
Gustav BrockCIOCommented:
Like I showed, without using Right().

/gustav
0
 
Anthony6890Author Commented:
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
 
Gustav BrockCIOCommented:
Yes, I thought it was 3 - from your example data.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
PatHartmanCommented:
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
 
Anthony6890Author Commented:
Gustav, your fn worked.  I adjusted the addmonth to one month prior and it all works well.  Thank you.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
All Courses

From novice to tech pro — start learning today.