Calculate Month/Years in "Future" with Given Starting Date 1

[RP1America]

Greetings!

I am looking for a way to automate displaying 'payments due back' dates within a letter.

I have a userform field that asks the user for the 'number of payments due back'. Another section of the userform asks for the 'date of first payment due back' (3 numeric textboxes; MM, DD, YYYY).

I am setting up an IF statement so that if there are 4 or more payments due back, the text will show "(date of first payment due back) through (date of last payment due back)". In order to do this, I need a way to calculate what the date of last payment due back would be (i.e. if date of first payment due back is 11/01/2008 and there are 4 payments due back, the date of last payment due back would be 02/01/2009).

I need to be able to basically calculate what month and year it would be (as day will always remain the same) even if there were 50+ or 100+ payments due back.

Any suggestions?

Thanks!!

Dim strpdbtext As String
If numpytsdueback <= "4" Then strpdbtext = " payments, due " _
+ strpdbmonth + " " + pdbdd + ", " + pdbyyyy + " through " + ????

 

[SkipVought]

Hi,

Check out the DateSerial function. You can very simply add to the month value, given a year, month, day & nbr payments.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi RP1America,

To see how to do this and just about everything else you might want to do with dates in Word without vba, check out my Word Date Calculation Tutorial, at:

http://www.wopr.com/index.php?showtopic=249902

or

http://www.gmayor.com/downloads.htm#Third_party

Do read the document's introductory material.


Cheers
[MS MVP - Word]

 

[RP1America]

Check out the DateSerial function. You can very simply add to the month value, given a year, month, day & nbr payments.

Hmm...suppose I'm having trouble with the "very simply" part.

I've looked into DateSerial, yet I am having trouble getting the variables to 'take'.

    Dim strenddate As String
    strendate = DateSerial(pdbyyyy, pdbmm + numpytsdueback.Value, pdbdd)

I also tried DateAdd, unfortunately I'm just not understand what needs to go where.

    Dim strdate As String
    strdate = pdbmm + "/" + pdbdd + "/" + pdbyyyy

    Dim months As Double
    Dim seconddate As Date
    seconddate = CDate(strdate)
    months = Val(numpytsdueback)
    Dim strenddate As String
    strenddate = DateAdd(DateInterval.Month, months, seconddate)

I'm feeling a bit overwhelmed and undereducated.

 

[SkipVought]

First, dates are not STRINGS. They are NUMBERS. Today, for instance, the DateSerial value is 40086. 40,085 days since 1/1/1900).

Te DateSerial funciton needs THREE numeric arguments: Year, Month and Day...

    Dim dEndDate As Date

    dEndDate = DateSerial(pdbyyyy, pdbmm + numpytsdueback.Value, pdbdd)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RP1America]

For some reason, I couldn't get the code that you suggested to calculate correctly.

However, I did get this to work:

    Dim strstartdate As String
    strstartdate = pdbmm.Value + "/" + pdbdd.Value + "/" + pdbyyyy.Value

    Dim dEndDate As Date
    dEndDate = DateSerial(Year([strstartdate]), Month([strstartdate]) + numpytsdueback.Value - 1, Day([strstartdate]))

Now, the issue is that it is giving me the value in m/d/yyyy format. I need dEndDate to show up in my letter as MMMM d, yyyy format. Any ideas?

 

[SkipVought]

dim sMyLetterDate as [b]String[/b]

sMyLetterDate = Format(dEndDate, "MMMM d, yyyy")

FYI, Format returns a STRING and not a DATE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Anyway, why not simply this ?

dEndDate = DateSerial(pdbyyyy.Value, pdbmm.Value + numpytsdueback.Value - 1, pdbdd.Value)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pjm]

Watch your variable names!!!

Dim strend[red]d[/red]ate As String
strendate = DateSerial(pdbyyyy, pdbmm + numpytsdueback.Value, pdbdd)

You should always use Option Explicit in your modules!

pjm

 

[fumei]

You should always use Option Explicit in your modules!"

Always always always

Although, to be fair, it is very possible that some of the code posted here is typed in directly here, and is not the actual executing code. Hopefully that is the case, and Option Explicit is being used.

None the less, yup, naming things well is both an art, and a requirement.

Skip, I am shocked - SHOCKED! - you have not posted the link to your "why are dates such a pain in the butt" FAQ.

As usual, PHV seems to be offering the best solution.

RP1, it is critical that you grasp the difference (as Skip has mentioned) between DATES (numeric) and strings that look like dates...i.e. Formated to look like dates. They may look like dates, but they are not real dates.


"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

Weeell Ok, Pilgrim...

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Now that is a pilgrim's progress.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

Watch out for that bunyon!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]