Checking years and months 2

[slobad23]

I have 2 fields in a report that allows me to script fields with VB. The fields come from SQL which are calulated like this:

Years:
DATEDIFF(MONTH,EFFECTIVEDATE, ISNULL(ENDDATE,GETDATE())) / 12

Months:
DATEDIFF(MONTH,EFFECTIVEDATE,ISNULL(ENDDATE,GETDATE())) % 12

What I am getting in the report is this situation:

John Smith
Years: 5
Months: 4

Years: 2
Months: 11

Sum fields: 7 years, 15 months

What I would really like to do and don't know how to manage this in vbscript, is to check the months field and update the years accordingly.

 

[PHV]

A starting point:
Y1=5: Y2=2: M1=4: M2=11
SumY=Y1+Y2-(M1+M2>=12)
SumM=M1+M2+12*(M1+M2>=12)

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

 

[slobad23]

Starting point === Exact solution.

Thank you for this. True = 0 and false = -1 is good to know. I'm sure that will come in handy a lot more in the future with these reports.


Many thanks!

 

[mikrom]

PHV, thank you very much for this interesting example.

True = 0 and false = -1 is good to know.

No, True seems to be -1 - look at this:

Y1=5: Y2=2: M1=4: M2=11
x = M1+M2>=12
wscript.echo "M1+M2>=12 = " & x
y = 0 + (M1+M2>=12)
wscript.echo "M1+M2>=12 = " & y

SumY=Y1+Y2-(M1+M2>=12)
SumM=M1+M2+12*(M1+M2>=12)
wscript.echo "SumY = " & SumY
wscript.echo "SumM = " & SumM

It delivers

M1+M2>=12 = True
M1+M2>=12 = -1
SumY = 8
SumM = 3

 

[BigRed1212]

Just for the different way to skin the cat department:

dim y1, y2, m1, m2
y1=5
y2=2
m1=11
m2=11
response.write "Equals=" & y1+y2+int((m1+m2)/12) & " years " & (m1+m2) mod 12 & " months<br />"

delivers
Equals=8 years 10 months

 

[strongm]

>True seems to be -1

More generally:
False = 0
True is not False


>Just for the different way
And here's a couple more ...

[blue]    Dim mydate
    mydate = DateSerial(y1 + y2, m1 + m2, 1)
    SumY = DatePart("yyyy", mydate) - 2000
    SumM = DatePart("m", mydate)
    
    
    Dim months
    months = (y1 + y2) * 12 + m1 + m2
    SumY = months \ 12
    SumM = months Mod 12[/blue]