Eligibility date calculation

C

CarrieStory

I have created a form in which a person fills in a hire date in a form box.
I would like to calculate a benefit effective date which is equal to the
first of the next month following 90 days of employment. I have no idea
where to begin. I would like the date to calculate automatically when the
person fills in the form. Please help!
 
K

Karl E. Peterson

CarrieStory said:
I have created a form in which a person fills in a hire date in a form box.
I would like to calculate a benefit effective date which is equal to the
first of the next month following 90 days of employment. I have no idea
where to begin. I would like the date to calculate automatically when the
person fills in the form. Please help!
Click to expand...

How about?

Eligibility = DateSerial(Year(HireDate), Month(HireDate) + 4, 1)

You might need to tweak in cases where the hire date *is* the first of the month.
IOW, say someone is hired on March 1, would this eligibility date then be June 1 or
July 1? If the former, the full function would be:

Eligibility = _
DateSerial(Year(HireDate), Month(HireDate) + Iif(Day(HireDate) > 1, 4, 3), 1)

Later... Karl
 
C

CarrieStory

Hi Karl, thank you very much. I understand what your logic means, but i
don't understand how to put it in my document. I'm sorry, I'm just a
beginner. Can you please explain?
 
K

Karl E. Peterson

CarrieStory said:
Hi Karl, thank you very much. I understand what your logic means, but i
don't understand how to put it in my document. I'm sorry, I'm just a
beginner. Can you please explain?
Click to expand...

My turn to be honest. I get the logic, but I'm not much up on document automation.
(This is where a good "Bones" impersonation would be appropos... "Dammit, I'm a
programmer, Jim, not a scripter!" <g>) Hopefully, someone with more Word experience
than I can correct what follows, and is certainly only a best guess.

I'd assume, given your original description, that you're creating a UserForm in VBA,
right? If that's the case, then you'd want to monitor change conditions in the
textbox (or calendar control?) that's used to enter the hire date. When change
happens, bam!, you invoke the logic below by A) interpreting the data entered by the
user, coercing it into a sensible Date value, then B) plugging that into the formula
below and spitting the results into whatever field you have assigned for this value.

Okay, this is where I bow before the Wordies who normally haunt this group. HTH!
 
C

CarrieStory

Funny you say that, that is where i am right now! Your information is
fantastic except i am a beginner. I don't understand the verbage. I just
posted the same question in Woody's lounge under Date Calculation.

macropod said:
Hi Carrie,

For field-based (ie non-macro) calculations for just about everything you might want to do with dates in Word, check out my Word
Date Calculation Tutorial, at:
http://www.wopr.com/index.php?showtopic=249902&st=0&p=249902&#entry249902
or
http://www.gmayor.com/downloads.htm#Third_party
Do read the document's introductory material.


--
Cheers
macropod
[MVP - Microsoft Word]


CarrieStory said:
I have created a form in which a person fills in a hire date in a form box.
I would like to calculate a benefit effective date which is equal to the
first of the next month following 90 days of employment. I have no idea
where to begin. I would like the date to calculate automatically when the
person fills in the form. Please help!
Click to expand...
Click to expand...
 
D

Doug Robbins - Word MVP on news.microsoft.com

Assuming that your form is a document that is protected for filling in forms
and that you have a formfield with the bookmark name of HireDate that is set
as a Date type formfield with the format of the date set to d MMMM yyyy into
which the hire date is inserted and that you have a second formfield with
the bookmark name of EligibilityDate, also of the Date type with the same
formatting, if you run a macro containing the following code on exit from
the HireDate formfield, it will populate the EligibilityDate formfield with
the required date.

Dim sDate As Date, eDate As Date
With ActiveDocument
sDate = .FormFields("HireDate").result
If IsDate(sDate) Then
eDate = DateAdd("d", 90, sDate)
If Format(eDate, "d") > 1 Then
eDate = DateAdd("d", 30, Format(eDate, "d MMMM yyyy"))
.FormFields("EligibilityDate").result = Format(eDate, "d") -
Format(eDate, "d") _
+ 1 & " " & Format(eDate, "MMMM yyyy")
Else
.FormFields("EligibilityDate").result = Format(eDate, "d MMMM
yyyy")
End If
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
D

Doug Robbins - Word MVP on news.microsoft.com

Hi Karl

And a bit more tweaking if Month(HireDate) + 3 (or 4) > 12

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
K

Karl E. Peterson

Doug said:
And a bit more tweaking if Month(HireDate) + 3 (or 4) > 12
Click to expand...

You would think, wouldn't you? Doesn't matter.

?dateserial(2008, 15, 6)
3/6/2009

So much for overthinking stuff. <g>
 
D

Doug Robbins - Word MVP on news.microsoft.com

Right you are. Looking more closely at the VBA Help file, I see that it
only says "should be" not "must be".

the range of numbers for each DateSerial argument should be in the accepted
range for the unit; that is, 1-31 for days and 1-12 for months.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
K

Karl E. Peterson

Doug said:
Right you are. Looking more closely at the VBA Help file, I see that it
only says "should be" not "must be".

the range of numbers for each DateSerial argument should be in the accepted
range for the unit; that is, 1-31 for days and 1-12 for months.
Click to expand...

Yeah, but if you really want to get the most milage from that function, you need to
step outside those windows. For example, to find the "last day of the month" you
just do:

DateSerial(Year(dt), Month(dt) + 1, 0)

where dt is any date in the target month.
 
G

Greg Maxey

How about?

   Eligibility = DateSerial(Year(HireDate), Month(HireDate) + 4, 1)

You might need to tweak in cases where the hire date *is* the first of the month.
IOW, say someone is hired on March 1, would this eligibility date then beJune 1 or
July 1?  If the former, the full function would be:

   Eligibility = _
      DateSerial(Year(HireDate), Month(HireDate) + Iif(Day(HireDate) > 1, 4, 3), 1)

Later...   Karl
Click to expand...

Karl,

Realizing that I am Johnny Come Late in this discussion but wouldn't
the first day of the "next" month following 90 days of employment
require something like this:

Sub FFOnExit()
Dim dt As Date
On Error GoTo Err_Handler
dt = ActiveDocument.FormFields("Text1").Range.Text
dt = DateSerial(Year(dt), Month(dt), Day(dt) + 90)
dt = DateSerial(Year(dt), Month(dt) + 1, 1)
ActiveDocument.FormFields("Text2").Result = dt
Exit Sub
Err_Handler:
MsgBox "Please enter a valid date"
ActiveDocument.FormFields("Text1").Range.Select
End Sub

So if an employee was hired on 12/1/2008, they were employed something
less than 31 full days in Dec, 31 days in Jan, 28 days in Feb. They
completed 90 days employment on 1 March 2009. The first day of the
month after that is 4/01/2009. Could be something for the Labor &
Management Team to work out ;-)
 
D

Doug Robbins - Word MVP on news.microsoft.com

Thanks, Karl.

Good to know.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
D

Doug Robbins - Word MVP on news.microsoft.com

Greg,

If the first entered date is considered to be one of the 90 days, then I
believe that it probably should be

dt = DateSerial(Year(dt), Month(dt), Day(dt) + 89)

Otherwise, OK

Many miserable HR departments would be likely to require it be +90 however.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

How about?

Eligibility = DateSerial(Year(HireDate), Month(HireDate) + 4, 1)

You might need to tweak in cases where the hire date *is* the first of the
month.
IOW, say someone is hired on March 1, would this eligibility date then be
June 1 or
July 1? If the former, the full function would be:

Eligibility = _
DateSerial(Year(HireDate), Month(HireDate) + Iif(Day(HireDate) > 1, 4, 3),
1)

Later... Karl
Click to expand...

Karl,

Realizing that I am Johnny Come Late in this discussion but wouldn't
the first day of the "next" month following 90 days of employment
require something like this:

Sub FFOnExit()
Dim dt As Date
On Error GoTo Err_Handler
dt = ActiveDocument.FormFields("Text1").Range.Text
dt = DateSerial(Year(dt), Month(dt), Day(dt) + 90)
dt = DateSerial(Year(dt), Month(dt) + 1, 1)
ActiveDocument.FormFields("Text2").Result = dt
Exit Sub
Err_Handler:
MsgBox "Please enter a valid date"
ActiveDocument.FormFields("Text1").Range.Select
End Sub

So if an employee was hired on 12/1/2008, they were employed something
less than 31 full days in Dec, 31 days in Jan, 28 days in Feb. They
completed 90 days employment on 1 March 2009. The first day of the
month after that is 4/01/2009. Could be something for the Labor &
Management Team to work out ;-)
 
G

Greg Maxey

Doug,

Yes, I used 90 with the view that unless a person is hired precisely at
00:00 on day 1 then 90 (full) days isn't actually completed until some time
on day 91. I guess that I am starting off from the position of one of those
miserable HR departments ;-). Having the unfortunate experience of
participation on a command management and labor team in my former life I
learned that management initially gives nothing as the union always wants
and will take everything. I can envision days of seemingly endless team
debate over this point, several grievances files, and perhaps a formal
unfair labor practice suit.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display weeks from a Milestone 0
Date diffrence in days, weeks and months. 1
Countdown macro - display settings 2
Turning off automatic calculation 0
Doughnut Chart linked with the slicer in the pivot table 0
Eligibility Dates 3
Creating Forms 0
Calculate eligibility/enrollment date 8

Top