Answered by:
Convert an integer to # of yearsmonthsdays

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
Question
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
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
 Moved by Mike Feng Friday, November 23, 2012 9:20 AM (From:.NET Platform Architecture Development Discussions)
 Merged by Mike YinMicrosoft contingent staff, Moderator Thursday, November 29, 2012 7:42 AM duplicate

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/CalculatingDurationBetweenTwoDatesinYearsMo 
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)
 Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Monday, November 26, 2012 4:08 PM


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

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

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

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




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
 Edited by Syed Qazafi AnjumModerator Wednesday, December 05, 2012 9:28 PM
 Proposed as answer by Shahfaisal MuhammedModerator Thursday, December 06, 2012 2:30 AM

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