none
Convert an integer to # of years-months-days

    Question

  • I need to convert a whole number to the number of years, days and months.
    Example: 4142= 11 years, 4, months and 4 days (give or take a day)

    in my text box i have a

    =avg(Fields!XXX.value)

    so i want to convert inside my expression..is there anyways to do that..i mean to do 2 calculation at a time in my expression?

    thanks in advance

    Sunday, November 25, 2012 10:05 PM

Answers

  • Hi There

    Thanks for your posting. You can try something like this

    Public Function NaturalLength(length  As Integer ) As String
            
                  dim length1 As string=0
                  dim length2 As string=0
                  dim length3 As string=0
                  dim ReturnString As string
    ReturnString=””
    If length = 0 then
    Return String.Empty
    end if
    if length >= 366
    length1=cstr(Math.Floor (length / 365.25)*12)
    length  =(length  Mod 365.25)
    end if
    If length > 31 AndAlso length < 366 Then
    length2 =cstr((Math.Floor(length / 30.4375 )) )
    length  =length  Mod 30.4375
    end if
    if length < 31 Then
    length3 =cstr(length) 
    End If
    If cint(length1) >0
    ReturnString= length1+" Years "
    End if
    If cint(length2) >0
    ReturnString= ReturnString + length2+ "  Months "
    End if
    If cint(length3) >0
    ReturnString= ReturnString + length3 + "  days"
    End if
    Return (ReturnString)
    End Function

    many thanks

    Syed

    • Marked as answer by uttar007 Friday, November 30, 2012 9:42 PM
    Thursday, November 29, 2012 10:25 PM

All replies

  •  i have an average value, i would like to show in months,days and years if possible.if i have 282.4days rounded is 282.4/30=9months 13days, i want something like this in my ssrs 2008.

    my output should be like this

    test 1          average

    =================

    761days      580.5

    that 761 days i get it from my custom code. here i use. doesn't matter if leap year or 31 days..

    1 year =365

    1 month =30 and day

    i want to perform this calculation in my custom code in sql reporting system 2008..

    thanks in advance


    A.Vadz

    Thursday, November 22, 2012 3:43 AM
  • Uttar :
    Can you add your own custom function ?
    Try this as start.
     Public Function GetCustomDate(ByVal days As Int32) As String
            Dim objDate1 As DateTime = DateTime.Now()
            Dim objDate2 As DateTime = objDate1.AddDays(-1 * days)        
    'This has to be modified as not correct.
            Return "Years = " & (objDate1.Year - objDate2.Year) & " Months = " & (objDate1.Month - objDate2.Month) & " Days = " & (objDate1.Day - objDate2.Day)
        End Function

    Pl refer to the URL below and test if it fits your need 
    http://www.codeproject.com/Articles/28837/Calculating-Duration-Between-Two-Dates-in-Years-Mo
    Monday, November 26, 2012 3:55 AM
  • Try this code:

    Public Function Days2YMD(length As integer) As String
    ' days 0 - 60
    ' months 61 - 730
    ' years 731 plus
    dim length1 As string=0
    dim length2 As string=0
    dim length3 As string=0
    If length = 0 then
    Return String.Empty
    end if
    if length >= 731 
    length1=cstr(Math.Floor (length / 365.25))
    length  =(length  Mod 365.25)
    end if
    If length > 60 AndAlso length < 731 Then
    length2 =cstr((Math.Floor(length / 30.4375 )) )
    length  =length  Mod 30.4375
    end if
    if length < 61 Then
    length3 =cstr(length)  
    End If
    Return (length1+" Years " +length2+ "  Months " +length3 + "  days")
    End Function

    Use it:

    =code.Days2YMD(365)
    Monday, November 26, 2012 3:45 PM
  • hi

    thanks for your reply...i got my results but i want to be like this,if i got 365Days. my output looks like year=1 month=0 day=0..i want to ask if i can get only year =1 rather than include both month(s) and day(s) since both a 0(s)

    thanks

    Tuesday, November 27, 2012 2:00 AM
  • Uttar :  Can you add if condition(s) and display you result as you already have the year month and day values.  Like below to start..

    Dim result As String = ""

    If year <> 0 Then
      result = "year = " & year
    End If
    If month <> 0 Then
      result = result & " month = " & month 
    End If
    If day <> 0 Then
      result =  result & " day = " & day
    End If


     

    Tuesday, November 27, 2012 2:22 AM
  • Try this:

    Public Function Days2YMD(ByVal days As Int32) As String
    Dim objDate1 As DateTime = DateTime.Now()
    Dim objDate2 As DateTime = objDate1.AddDays(-1 * days)        
    Dim result as string
    If (objDate1.Year - objDate2.Year) <> 0 Then
      result = "year = " & (objDate1.Year - objDate2.Year) 
    End If
    If (objDate1.Month - objDate2.Month) <> 0 Then
      result = result & " month = " & (objDate1.Month - objDate2.Month) 
    End If
    If (objDate1.Day - objDate2.Day)<> 0 Then
      result =  result & " day = " & (objDate1.Day - objDate2.Day)
    End If
    return result
    End Function
    Thursday, November 29, 2012 3:19 PM
  • hi irusul,

     thanks for your reply..i try it

    this is what i got if i select those person who attending from 18/11/2012 - 20/11/2012. i have got it but my days seems not give the right values.if i got 61 days it gives me 2month not 2 months 1day, example of my output  is down below

    61days = 2month  #  it should be 2months 1day

    152=4month 29days #4months 32days

    547=1year 5month 28day

    thanks

    • Marked as answer by uttar007 Friday, November 30, 2012 9:42 PM
    • Unmarked as answer by uttar007 Friday, November 30, 2012 9:42 PM
    Thursday, November 29, 2012 8:36 PM
  • Hi There

    Thanks for your posting. You can try something like this

    Public Function NaturalLength(length  As Integer ) As String
            
                  dim length1 As string=0
                  dim length2 As string=0
                  dim length3 As string=0
                  dim ReturnString As string
    ReturnString=””
    If length = 0 then
    Return String.Empty
    end if
    if length >= 366
    length1=cstr(Math.Floor (length / 365.25)*12)
    length  =(length  Mod 365.25)
    end if
    If length > 31 AndAlso length < 366 Then
    length2 =cstr((Math.Floor(length / 30.4375 )) )
    length  =length  Mod 30.4375
    end if
    if length < 31 Then
    length3 =cstr(length) 
    End If
    If cint(length1) >0
    ReturnString= length1+" Years "
    End if
    If cint(length2) >0
    ReturnString= ReturnString + length2+ "  Months "
    End if
    If cint(length3) >0
    ReturnString= ReturnString + length3 + "  days"
    End if
    Return (ReturnString)
    End Function

    many thanks

    Syed

    • Marked as answer by uttar007 Friday, November 30, 2012 9:42 PM
    Thursday, November 29, 2012 10:25 PM
  • hi syed

    thanks alot...:)

    Friday, November 30, 2012 3:36 AM
  • Hi Uttat007

    Thanks for your posting. Please mark the thread as answer if it resolved your problem.

    Many thanks

    Syed

    Friday, November 30, 2012 8:51 AM
  • all works great but if i have say 395days: my output display as 1 year 30 days which is fine but i want to display 30 as 1 month...as it should be 1 year 1month...like 30days to 1 month or 365days to be 1 year..others works fine

    thanks

    Wednesday, December 05, 2012 8:33 PM
  • Hi There

    Thanks for your posting. Please have a look on this and see if this resolve your problem

    Public Function NaturalLength(length  As Integer ) As String
            
                  dim length1 As string=0
                  dim length2 As string=0
                  dim length3 As string=0
                  dim ReturnString As string
    ReturnString=””
    If length = 0 then
    Return String.Empty
    end if
    if length >= 366
    length1=cstr(Math.Floor (length / 365.25))
    length  =(length  Mod 365.25)
    end if
    If length >= 30 AndAlso length < 366 Then
    length2 =cstr((Math.Floor(length / 30)) )
    length  =length  Mod 30
    end if
    if length < 30 Then
    length3 =cstr(length) 
    End If
    If cint(length1) >0
    ReturnString= length1+" Years "
    End if
    If cint(length2) >0
    ReturnString= ReturnString + length2+ "  Months "
    End if
    If cint(length3) >0
    ReturnString= ReturnString + length3 + "  days"
    End if
    Return (ReturnString)
    End Function

    I hope this will help

    Please mark the thread as answer if it resolved your problem.

    Many thanks

    Syed


    Wednesday, December 05, 2012 9:24 PM
  • hi..

    thanks for your reply 

    if i have 1 day..why it is increment to 1 this happens if i have month and days or include all year,months and days..year and day looks fine.2years 2 days=2 years 2days ..or if i enter 1 days only it display as 1 day but 3 months 1 day is display 3months 2 days..or 1year 2months 1 day display as 1 year 2months and 2 days...

    please help..

    thanks in advance

    Monday, December 10, 2012 2:31 AM