Working with months and years 2

[saintedmunds]

Hi

I have a function thats works out someones age in years and months ie 10.9 now i need to able to do the following.

13.3 minus 10.11 which I want to = 2.4 but returns 3.19

How Can I get it to reurn what I need?

Cheers

 

[Remou]

You are not really talking about .11 years, you are talking about 11 months. I think it would be much easier to take a step back and leave the decimal point out. Then you could multiply the years by twelve and subtract months from months. Here are a few notes:

bd1 = 13.3
bd2 = 10.11

bd1Months = (Round(bd1, 0) * 12) + Mid(Str(bd1), InStr(bd1, ".") + 2)
bd2Months = (Round(bd2, 0) * 12) + Mid(Str(bd2), InStr(bd2, ".") + 2)

'Which you have written as
Debug.Print (bd1Months - bd2Months) \ 12 & "." & (bd1Months - bd2Months) Mod 12
'but is really
Debug.Print (bd1Months - bd2Months) \ 12 & " years and " _
    & (bd1Months - bd2Months) Mod 12 & " months"

 

[saintedmunds]

Hi

Thank you for your reply but I have a couple of issues.

If I do 13.3 - 10.9 I get 1.6 not 2.6
If I do 14 - 11 I get 3.3 not 3


Can you help with the above?

Cheers

 

[Remou]

I should have used Int:
[tt]bd1Months = (Int(bd1) * 12) + _
IIf(InStr(Str(bd1), ".") > 0, Mid(Str(bd1), InStr(bd1, ".") + 2), 0)
bd2Months = (Int(bd2) * 12) + _
IIf(InStr(Str(bd2), ".") > 0, Mid(Str(bd2), InStr(bd2, ".") + 2), 0)[/tt]

 

[SgtJarrow]

My first question is if you have a function already that gives you the results of the age in Years.Months, do you have access to the actual dates? This is the most logical place to start. All you have to do is run a DateDiff between the BirthDate and whatever date you want, then format that into years and months.

That being said, or if you don't have access to the actual date values, then the below code should be able to make your conversions for you:

Public Function CalculateAgeDifference(strStartValue As String, strEndValue As String) As String

    Dim intYears As Integer
    Dim intMonths As Integer
    
    intYears = CInt(Left(strStartValue, InStr(1, strStartValue, ".") - 1)) - CInt(Left(strEndValue, InStr(1, strEndValue, ".") - 1))
    intMonths = CInt(Mid(strStartValue, InStr(1, strStartValue, ".") + 1)) - CInt(Mid(strEndValue, InStr(1, strEndValue, ".") + 1))
    
    If intMonths < 0 Then
        intYears = intYears - 1
        intMonths = intMonths + 12
    End If
    
    CalculateAgeDifference = CStr(intYears) & "." & CStr(intMonths)

End Function

Just drop that into a module in your access app and use it like:

[field] = CalculateAgeDifference([firstvalue], [secondvalue])

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[saintedmunds]

Ok

Thank you both they both work upto a point.
Put if I have 9.6 - 10.3 the answer should be -0.9

Remou answer gives 0.-9

and

mstrmage1768 answer gives -1.3

Any Ideas

 

[SgtJarrow]

You didn't say anything about the possibilty of the end value being greater than the start value....again, here is why DateDiff would be best. I'll see if my above can be modified :-(, but I am not garanteeing an immediate response as it will take a bit of working


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Remou]

I think that we are both saying (well, I certainly am) that it would be much easier to go back to the point at which you obtain months and years, rather than trying to recover the information from a spurious number. I think you should post your code, rather than trying to fix up snippets that I posted more to comment than for use.

 

[saintedmunds]

Hi There

I dont have any Dates to go from they are test that are done to work out there ability in years and months and i need the diffrence.

Do I make sense?

 

[SgtJarrow]

I was totally on the same track as Remou. If you are getting a years and months total from the other function as you stated, there have to be dates somewhere. That is the best place to start for something like this.

Again, with that being said - I think this will do what you want. Give it a shot and let me know. I only tested it on a few different values, but they all seemed to check okay.

Public Function CalcAgeDiff(strStartValue As String, strEndValue As String) As String

    Dim intStartMonths As Integer
    Dim intEndMonths As Integer
    Dim intMonthsDifference As Integer
    
    intStartMonths = (CInt(Left(strStartValue, InStr(1, strStartValue, ".") - 1)) * 12) + CInt(Mid(strStartValue, InStr(1, strStartValue, ".") + 1))
    intEndMonths = (CInt(Left(strEndValue, InStr(1, strEndValue, ".") - 1)) * 12) + CInt(Mid(strEndValue, InStr(1, strEndValue, ".") + 1))
    
    intMonthsDifference = intStartMonths - intEndMonths
   
    If intMonthsDifference >= 0 Then
        CalcAgeDiff = CStr(intMonthsDifference \ 12) & "." & CStr(intMonthsDifference Mod 12)
    Else
        CalcAgeDiff = "-" & CStr(-1 * (intMonthsDifference \ 12)) & "." & CStr(-1 * (intMonthsDifference Mod 12))
    End If
    
End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SkipVought]

How about

    Dim d1, d2, n1, n2
    n1 = 13.3
    n2 = 10.11
    d1 = Split(n1, ".")
    d2 = Split(n2, ".")
    
    MsgBox (d1(0) + d1(1) / 12) - (d2(0) + d2(1) / 12)

Skip,

_[red][/red]

 

[SgtJarrow]

That is why Skip is so valuable to these forums....Much easier code. Thanks Skip!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[saintedmunds]

Thank you all for your help

I have used mstrmage1768 code as this gave me what I wanted

SkipVought I could not get your code to work as i needed.

Thanks again

 

[PHV]

My way:
d1=9.6
d2=10.3
m=(12*Int(d1)+Split(d1, ",")(1))-(12*Int(d2)+Split(d2, ",")(1))
d0=Left(Sgn(m),1) & Fix(Abs(m)/12) & "." & (Abs(m)-12*Fix(Abs(m)/12))
MsgBox d0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, forgot to delocalize ...
d1=9.6
d2=10.3
m=(12*Int(d1)+Split(d1,".")(1))-(12*Int(d2)+Split(d2,".")(1))
d0=Left(Sgn(m),1) & Fix(Abs(m)/12) & "." & (Abs(m)-12*Fix(Abs(m)/12))
MsgBox d0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Might as well post the correction to my version, I suppose.
[tt]bd1Months = (Int(bd1) * 12) + _
IIf(InStr(Str(bd1), ".") > 0, Mid(Str(bd1), InStr(bd1, ".") + 2), 0)
bd2Months = (Int(bd2) * 12) + _
IIf(InStr(Str(bd2), ".") > 0, Mid(Str(bd2), InStr(bd2, ".") + 2), 0)

strYears = Abs(bd1Months - bd2Months) \ 12
strMonths = Abs(bd1Months - bd2Months) Mod 12

Debug.Print (Val(strYears & "." & strMonths)) * Sgn(bd1 - bd2)[/tt]