Calculating exact time in Years, MOnths, Days-VBForums

# Thread: Calculating exact time in Years, MOnths, Days

1. ## Calculating exact time in Years, MOnths, Days

I am trying to calculate the exact amount of time in Years, Months and Days between 2 date values. I'm getting stuck on the day calculation. I've returned the # of months and it's value is 8.77. The .77 needs to be rounded to 8 which should give me the days. I'm not exactly sure what MATH function to use to do this. Any ideas?

Thanks,

2. ## Re: Calculating exact time in Years, MOnths, Days

Try this.

edit: 5 days later

If you have ever wondered why TimeSpan only reports to a resolution of days, this thread is for you. It is highly recommended that this thread be read.

The purpose of the code that follows is to add the concept of years, months, weeks and days as another way of looking at the difference between two dates. But this concept, and the code that follows, has definitions about years, months, and days which can best be shown through examples.

0 years, 0 months, 1 days between 1/30/2004 1/31/2004
0 years, 3 months, 0 days between 1/30/2004 4/30/2004
1 years, 0 months, 0 days between 1/30/2004 1/30/2005

Let’s also take a look at leap year:

0 years, 11 months, 30 days between 2/29/2004 2/28/2005
1 years, 0 months, 0 days between 2/29/2004 3/1/2005 – A leapling’s (someone born on 2/29/leap_year) birthday is 3/1 for non-leap years in this implementation.

Now the concept gets controversial.

0 years, 3 months, 0 days between 1/31/2004 4/30/2004 which is the same result as 1/30/2004 4/30/2004. The explanation is that this code is designed to be “birthday” related, and if the birthday’s are different then the results appear inconsistent, but from the perspective of a birthday the results are consistent.

0 years, 2 months, 28 days between 1/31/2004 4/28/2004
0 years, 2 months, 29 days between 1/31/2004 4/29/2004
0 years, 3 months, 0 days between 1/31/2004 4/30/2004
0 years, 3 months, 1 days between 1/31/2004 5/1/2004
0 years, 3 months, 2 days between 1/31/2004 5/2/2004

0 years, 2 months, 29 days between 1/30/2004 4/28/2004
0 years, 2 months, 30 days between 1/30/2004 4/29/2004
0 years, 3 months, 0 days between 1/30/2004 4/30/2004
0 years, 3 months, 1 days between 1/30/2004 5/1/2004
0 years, 3 months, 2 days between 1/30/2004 5/2/2004

This apparent inconsistency only occurs for “birth dates” with a day component greater than 28.

Test Code for AgeCalc class
Code:
```    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
Debug.WriteLine("<<<<< Birthday Concept >>>>>")
Dim foo As New AgeCalc()
Dim birthday As DateTime = #2/29/2004#
Dim aDate As DateTime = birthday
Dim endDate As DateTime = #4/1/2005#
Dim bar As Age = foo.AgeInYearsMonthsDays(birthday, aDate)

Debug.WriteLine(String.Format("{0} years,  {1} months,  {2} days  between  {3}  {4} ", _
bar.Years, _
bar.Months, _
bar.Days, _
bar.DatePast.ToShortDateString, _
bar.DateFuture.ToShortDateString))
Loop

End Sub```

AgeCalc class
Thanks to Sitten Spynne, .paul., Evil_Giraffe, jay20aiii, and techgnome.

Code:
```''' <summary>
''' Calculates age based on two dates.  Age is inclusive of one of the dates.
''' </summary>
''' <remarks></remarks>
'''

Class AgeCalc
Public Age As New Age
''' <summary>
''' New
''' </summary>
''' <param name="DatePast">oldest date i.e. 1/1/1901</param>
''' <param name="DateFuture">youngest date i.e. 1/1/2011</param>
''' <remarks></remarks>
Public Sub New(DatePast As DateTime, DateFuture As DateTime)
Me.Age.ClearAge()
Me.Age.SetDateTime(DatePast, DateFuture)
Me.PastIsPast()
End Sub

Public Sub New()
Dim foo As DateTime = DateTime.Now
Me.Age.ClearAge()
Me.Age.SetDateTime(foo, foo)
End Sub

''' <summary>
''' Calculates age in years and days
''' </summary>
''' <returns>Age</returns>
''' <remarks></remarks>
Public Function AgeInYearsDays() As Age
Me.PastIsPast()
Me.Age.ClearAge()
Me.nextBDay = Me.NextBDayCalc
If Me.sameDay Then
Return Me.GetCurrentAge
End If
Dim ts As TimeSpan = Me.Age.DateFuture - Me.nextBDay
Me.Age.Days = ts.Days
Return Me.GetCurrentAge
End Function

Public Function AgeInYearsDays(DatePast As DateTime, DateFuture As DateTime) As Age
Me.Age.SetDateTime(DatePast, DateFuture)
Return Me.AgeInYearsDays
End Function

''' <summary>
''' Calculates age in years, months, and days
''' </summary>
''' <returns>Age</returns>
''' <remarks></remarks>
Public Function AgeInYearsMonthsDays() As Age
Me.PastIsPast()
Me.Age.ClearAge()
Me.nextBDay = Me.NextBDayCalc 'next birthday
If Me.sameDay Then
Return Me.GetCurrentAge
End If

Me.Anniversary = Me.nextBDay
Me.checkNextMonth()
Do While Me.nextBDay < Me.Age.DateFuture
Me.DayPlus1()
Loop
Return Me.GetCurrentAge
End Function

Public Function AgeInYearsMonthsDays(DatePast As DateTime, DateFuture As DateTime) As Age
Me.Age.SetDateTime(DatePast, DateFuture)
Return Me.AgeInYearsMonthsDays
End Function

Public Function AgeInYearsMonthsWeeksDays() As Age
Me.AgeInYearsMonthsDays()
Me.Age.Weeks = Math.DivRem(Me.Age.Days, 7, Me.Age.Days)
Return Me.GetCurrentAge
End Function

Public Function AgeInYearsMonthsWeeksDays(DatePast As DateTime, DateFuture As DateTime) As Age
Me.Age.SetDateTime(DatePast, DateFuture)
Return Me.AgeInYearsMonthsWeeksDays
End Function

Public Function AgeInYearsWeeksDays() As Age
Me.AgeInYearsDays()
Me.Age.Weeks = Math.DivRem(Me.Age.Days, 7, Me.Age.Days)
Return Me.GetCurrentAge
End Function

Public Function AgeInYearsWeeksDays(DatePast As DateTime, DateFuture As DateTime) As Age
Me.Age.SetDateTime(DatePast, DateFuture)
Return Me.AgeInYearsWeeksDays
End Function

Private nextBDay As DateTime 'next birthday
Private Anniversary As DateTime
Private nextMonth As DateTime

Private Function GetCurrentAge() As Age
Return Me.Age
End Function

Private Sub checkNextMonth()
If Me.Age.DatePast.Day = 29 AndAlso Me.nextMonth.Day = 28 AndAlso Me.Age.DatePast.Month = 2 AndAlso Me.nextMonth.Month = 2 Then
Me.nextMonth = DateSerial(Me.nextMonth.Year, 3, 1)
End If
End Sub

Private Sub DayPlus1()
Me.Age.Days += 1
Me.MonthPlus1()
End If
End Sub

Private Sub MonthPlus1()
Me.Age.Days = 0
Me.Age.Months += 1
Me.checkNextMonth()
If Me.Age.Months >= 12 Then Me.YearPlus1()
End Sub

Private Sub YearPlus1()
Me.Age.Years += 1
Me.Age.Months = 0
Me.Age.Days = 0
End Sub

Private Sub PastIsPast()
If Me.Age.DatePast > Me.Age.DateFuture Then 'make sure the past is the past
Me.Age.SetDateTime(Me.Age.DateFuture, Me.Age.DatePast)
End If
End Sub

Private Function sameDay() As Boolean
'If same day different years, then return years
If Me.Age.DatePast.Month = Me.Age.DateFuture.Month AndAlso _
Me.Age.DatePast.Day = Me.Age.DateFuture.Day Then
'calculate years
Me.Age.Years = Me.Age.DateFuture.Year - Me.Age.DatePast.Year
Return True
End If
Return False
End Function

Private Function NextBDayCalc() As DateTime
Dim rv As DateTime
'create next "birthday"
'note - the wonderful thing about DateSerial is that it takes care of
'leap Birthdays, AKA leaplings, (2/29/leapyear) . A leaplings b-day is 3/1 for non-leap years
rv = DateSerial(Me.Age.DateFuture.Year, Me.Age.DatePast.Month, Me.Age.DatePast.Day) 'takes care of leap b-day
If rv > Me.Age.DateFuture AndAlso Me.Age.DateFuture <> Me.Age.DatePast Then 'is the next "birthday" > future date
rv = DateSerial(Me.Age.DateFuture.Year - 1, Me.Age.DatePast.Month, Me.Age.DatePast.Day) 'takes care of leap b-day
End If
'calculate years
Me.Age.Years = rv.Year - Me.Age.DatePast.Year
Return rv
End Function
End Class

Class Age
'age
Public Years As Integer
Public Months As Integer
Public Weeks As Integer
Public Days As Integer

Public Sub New()
Dim foo As DateTime = DateTime.Now
Me.SetDateTime(foo, foo)
End Sub

Public Sub New(DatePast As DateTime, DateFuture As DateTime)
Me.SetDateTime(DatePast, DateFuture)
End Sub

Private _datepast As DateTime
Public Property DatePast() As DateTime
Get
Return Me._datepast
End Get
Set(ByVal value As DateTime)
Me.SetDateTime(value, Me.DateFuture)
End Set
End Property

Private _datefuture As DateTime
Public Property DateFuture() As DateTime
Get
Return Me._datefuture
End Get
Set(ByVal value As DateTime)
Me.SetDateTime(Me.DatePast, value)
End Set
End Property

Public ReadOnly Property Difference() As TimeSpan
Get
Return Me.DateFuture - Me.DatePast
End Get
End Property

Public Sub ClearAge()
Me.Years = 0
Me.Months = 0
Me.Weeks = 0
Me.Days = 0
End Sub

Public Sub SetDateTime(DatePast As DateTime, DateFuture As DateTime)
'note Time is not stored
If DatePast <= DateFuture Then
Me._datepast = DatePast.Date
Me._datefuture = DateFuture.Date
Else
Me._datepast = DateFuture.Date
Me._datefuture = DatePast.Date
End If
End Sub
End Class

Module DateExtensions
<System.Runtime.CompilerServices.Extension()> _
Public Function AddOneMonth(ByVal instance As Date) As DateTime
Dim rv As DateTime = DateSerial(instance.Year, instance.Month + 1, instance.Day)
If instance.Day <> rv.Day Then
rv = DateSerial(instance.Year, instance.Month + 1, Date.DaysInMonth(instance.Year, instance.Month + 1))
End If
Return rv
End Function
End Module```

3. ## Re: Calculating exact time in Years, MOnths, Days

Here is one...it is for age, but you should be able to modify it for your needs.

4. ## Re: Calculating exact time in Years, MOnths, Days

Thanks bro, but I know it can be done using alot less code...

5. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by blakemckenna
Thanks bro, but I know it can be done using alot less code...
I bet it can.

I haven't examined it closely for a long time. One thing I do remember was that leaplings always seemed to mess with my results, until I this code.

6. ## Re: Calculating exact time in Years, MOnths, Days

Like I said, the only thing I'm stuck with now is converting the remainder (.77)of 8.77 into a whole number and I just can't remember how to do that!

7. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by blakemckenna
Like I said, the only thing I'm stuck with now is converting the remainder (.77)of 8.77 into a whole number and I just can't remember how to do that!
What is 8.77?

Why not show us the code? You could use the code I posted as a check to see if the results match.

8. ## Re: Calculating exact time in Years, MOnths, Days

Well, if you have .77 of a month, that would mean you have 77&#37; of the days of a month. Depending on the month, it could be a couple different values. You can't just convert the .77 into an amount because you need to know what month that 77% is apart of:
Code:
```Feburary with 28 days = 21.56
Feburary with 29 days = 22.33
Months with 30 days   = 23.1
Months with 31 days   = 23.87```
Then you could use this "template" I guess to calculate the days:
Code:
```'//your month calculation
Dim months = 8.77D

'//gets the 0.77
Dim remainder = months Mod Math.Floor(months)

'//find the year and month, then get the days of that month
Dim days = Date.DaysInMonth(year, month) * remainder```
If the .77 is spanning across months, then it's a different ball game.

9. ## Re: Calculating exact time in Years, MOnths, Days

*edit* Note this code sample was proven wrong and corrected in #60, which has problems of its own. The discussion on page 2 is interesting. */edit*

I'd be wary of seeking "less code" in all circumstances. Smart programmers want "correct code". Good programmers care less about cramming everything into as few lines as possible and more about ensuring their results are provably correct.

.77 can be considered a percent. I don't think there's a reasonable way to convert that back to a number of days. Different months have different numbers of days. For example, if there were always 30 days in a month, you could say 8.77 months is 8 months and .77 * 30 = 23.1 days. But there's not 30 days in a month. Sometimes it's 31. Sometimes it's 30. Sometimes it's 28, and more rarely 29. Your first thought might be "I'll use how many days are in the 9th month", but if you pick September you'd only be right if your starting date is January 1. What you really want is "9 months from the beginning of the range", and even that can vary. What if the start date is July 31? I don't think there's any hope of getting correct results this way without writing dozens of extra lines of code for special cases.

In general, I feel like math against the calendar is always error-prone. The months are uneven, leap years are complicated (and change based on era!), and it's hard to understand if you're right. I prefer to lean on someone else's time implementation, especially if thousands of other programmers rely on it.

The easiest way to measure years, months, and days is to use the DateTime class and keep adding days. dbasnett's code makes a clever optimization I tried once and loops years, then months, then days. As you can see, that turns out to be more code. He does have an advantage I'll discuss after showing my code that loops only days. Here's a program, complete with tests, that seems to pull it off correctly without icky hard-to-verify calculations:
Code:
```Module Module1

Sub Main()
TestWithinMonth()
TestAcrossMonths()
TestLeapYear()
TestAcrossYears()
End Sub

Function CalculateRange(ByVal startDate As DateTime, ByVal endDate As DateTime) As TimePeriod
Dim period As New TimePeriod

Dim currentDate As DateTime = startDate
While currentDate < endDate
' Let a day elapse

' Determine if a month or year has elapsed.
If currentDate.Day = startDate.Day Then
If currentDate.Month = startDate.Month Then
' It's the same day and month in a new year: a year has elapsed.
period.YearElapsed()
Else
' It's the same day in a different month: a month has elapsed
period.MonthElapsed()
End If
Else
' It's not a month or day boundary
period.DayElapsed()
End If
End While

Return period
End Function

Sub TestWithinMonth()
Dim startDate As DateTime = New DateTime(2011, 1, 1)
Dim endDate As DateTime = New DateTime(2011, 1, 31)
Dim elapsed = CalculateRange(startDate, endDate)

Console.WriteLine("Jan 1 to Jan 31 should be 30 days. Result:")
PrintPeriod(elapsed)
End Sub

Sub TestAcrossMonths()
Dim startDate As New DateTime(2011, 1, 15)
Dim endDate As New DateTime(2011, 2, 15)
Dim elapsed = CalculateRange(startDate, endDate)

Console.WriteLine("Jan 15 to Feb 15 should be 1 month, 0 days. Result:")
PrintPeriod(elapsed)

endDate = New DateTime(2011, 4, 18)
elapsed = CalculateRange(startDate, endDate)
Console.WriteLine("Jan 15 to Apr 18 should be 3 months, 3 days. Result:")
PrintPeriod(elapsed)

endDate = New DateTime(2011, 4, 12)
elapsed = CalculateRange(startDate, endDate)
Console.WriteLine("Jan 15 to Apr 12 should be 2 months, 28 days.")
PrintPeriod(elapsed)
End Sub

Sub TestLeapYear()
Dim startDate As New DateTime(2000, 2, 28)
Dim endDate As New DateTime(2000, 3, 1)
Dim elapsed = CalculateRange(startDate, endDate)

Console.WriteLine("Feb 28 2000 to Mar 1 2000 should be 2 days.")
PrintPeriod(elapsed)
End Sub

Sub TestAcrossYears()
Dim startDate As New DateTime(2011, 1, 1)
Dim endDate As New DateTime(2012, 1, 1)
Dim elapsed = CalculateRange(startDate, endDate)

Console.WriteLine("Jan 1 2011 to Jan 1 2012 should be 1 year.")
PrintPeriod(elapsed)

endDate = New DateTime(2011, 12, 31)
elapsed = CalculateRange(startDate, endDate)
Console.WriteLine("Jan 1 2011 to Dec 31 2011 should be 11 months, 30 days.")
PrintPeriod(elapsed)

startDate = New DateTime(2011, 1, 15)
endDate = New DateTime(2013, 5, 14)
elapsed = CalculateRange(startDate, endDate)
Console.WriteLine("Jan 15 2011 to May 14 2013 should be 2 years, 3 months, 29 days.")
PrintPeriod(elapsed)
End Sub

Private Sub PrintPeriod(ByVal period As TimePeriod)
Console.WriteLine("{0} years, {1} months, {2} days", period.Years, period.Months, period.Days)
End Sub

End Module

Class TimePeriod
Public Property Days As Integer
Public Property Months As Integer
Public Property Years As Integer

Public Sub DayElapsed()
Days += 1
End Sub

Public Sub MonthElapsed()
Months += 1
Days = 0
End Sub

Public Sub YearElapsed()
Years += 1
Months = 0
Days = 0
End Sub
End Class```
My code's smaller and easier to understand, but it gets slow for very large timespans. The more days between the dates, the slower it gets. It's probably negligible for time periods of 100 years or so, but if you want more than that dbasnett's code is going to pull ahead. My technique's especially bad when you start wanting hours, minutes, and seconds for periods on the order of decades. dbasnett's is more likely to be correct than anything that tries to turn "8.77 months" into a reasonable time period.

I bet some work on the variable names in dbasnett's code could make it more approachable. Half of the complexity seems to stem from its support of some features that don't *need* to be there but are nice.

10. ## Re: Calculating exact time in Years, MOnths, Days

I updated post #2 in this thread. Still hoping to see less code.

11. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by blakemckenna
Thanks bro, but I know it can be done using alot less code...
There are times when attempting to do an operation with less code can make maintenance difficult which I have seen countless times with C developers who will do whatever it takes to condense a particular section of code, kind of like a badge of honor. Nothing wrong with this if you fully understand the code but if you do not because it is condensed then more code is better if you can understand that code. In addition, there are times when more lines of code can actually be faster.

12. ## Re: Calculating exact time in Years, MOnths, Days

here's my attempt at it:

vb Code:
`Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        'this counts inclusively. both the start day + end day are counted        Dim startDate As New Date(1969, 5, 3)        Dim endDate As Date = Now.Date         Dim totalYears As Integer        Dim totalMonths As Integer         Dim totalDays As Integer = CInt(Int((endDate - startDate).TotalDays))         If startDate.AddMonths(1) < endDate Then            totalDays -= (Date.DaysInMonth(startDate.Year, startDate.Month) - (startDate.Day - 1))            If totalDays > 0 Then totalMonths += 1 Else GoTo ignore             startDate = startDate.AddDays((Date.DaysInMonth(startDate.Year, startDate.Month) - startDate.Day))             Do                totalDays -= Date.DaysInMonth(startDate.Year, startDate.Month)                totalMonths += 1                startDate = startDate.AddMonths(1)            Loop While startDate <= endDate.AddMonths(-1)             totalYears = totalMonths \ 12            totalMonths = totalMonths Mod 12        Else            totalDays += 1            If totalDays >= Date.DaysInMonth(startDate.Year, startDate.Month) Then                totalDays -= Date.DaysInMonth(startDate.Year, startDate.Month)                totalMonths += 1            End If        End If ignore:         MsgBox(String.Format("Years: {0}{3}Months: {1}{3}Days: {2}", totalYears, totalMonths, totalDays, Environment.NewLine))     End Sub End Class`

13. ## Re: Calculating exact time in Years, MOnths, Days

@paul - try these date pairs

2/29/1904 3/2/1904
2/29/1904 4/30/1904

1/1/2011 1/3/2011
1/1/2011 2/3/2011

14. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by dbasnett
@paul - try these date pairs

2/29/1904 3/2/1904
2/29/1904 4/30/1904

1/1/2011 1/3/2011
1/1/2011 2/3/2011
ok. it didn't work, but this does...

vb Code:
`Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        'this counts inclusively. both the start day + end day are counted        Dim startDate As New Date(2011, 1, 1)        Dim endDate As Date = New Date(2011, 3, 2)         Dim totalYears As Integer        Dim totalMonths As Integer = CInt(DateDiff(DateInterval.Month, startDate, endDate))         Dim totalDays As Integer = CInt(Int((endDate - startDate).TotalDays)) + 1         Dim monthDaySum As Integer = (Enumerable.Range(0, totalMonths).Select(Function(i) _                                        Date.DaysInMonth(startDate.AddMonths(i).Year, startDate.AddMonths(i).Month))).Sum         If monthDaySum > 0 Then totalDays -= monthDaySum         totalYears = totalMonths \ 12        totalMonths = totalMonths Mod 12         MsgBox(String.Format("Years: {0}{3}Months: {1}{3}Days: {2}", totalYears, totalMonths, totalDays, Environment.NewLine))     End Sub End Class`

15. ## Re: Calculating exact time in Years, MOnths, Days

But these show x months 1 day.

1/1/2011 2/1/2011
1/1/2011 3/1/2011
1/1/2011 4/1/2011

Shouldn't they be x months 0 days?

And these show negative days. (the start date is a leapling)

2/29/2004 3/1/2004
2/29/2004 3/2/2004
2/29/2004 3/3/2004

I struggled with these same issues.

16. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by dbasnett
But these show x months 1 day.

1/1/2011 2/1/2011
1/1/2011 3/1/2011
1/1/2011 4/1/2011

Shouldn't they be x months 0 days?
they're inclusive of the start + end day

Originally Posted by dbasnett
And these show negative days. (the start date is a leapling)

2/29/2004 3/1/2004
2/29/2004 3/2/2004
2/29/2004 3/3/2004

I struggled with these same issues.
this should work now:

vb Code:
`Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        'this counts inclusively. both the start day + end day are counted        Dim startDate As New Date(2004, 2, 29)        Dim endDate As New Date(2004, 3, 1)                Dim totalYears As Integer        Dim totalMonths As Integer = CInt(DateDiff(DateInterval.Month, startDate, endDate))         Dim totalDays As Integer = CInt(Int((endDate - startDate).TotalDays)) + 1         If totalDays < Date.DaysInMonth(startDate.Year, startDate.Month) Then totalMonths = 0         Dim monthDaySum As Integer = (Enumerable.Range(0, totalMonths).Select(Function(i) _                                        Date.DaysInMonth(startDate.AddMonths(i).Year, startDate.AddMonths(i).Month))).Sum         If monthDaySum > 0 Then totalDays -= monthDaySum         totalYears = totalMonths \ 12        totalMonths = totalMonths Mod 12         MsgBox(String.Format("Years: {0}{3}Months: {1}{3}Days: {2}", totalYears, totalMonths, totalDays, Environment.NewLine))     End Sub End Class`

17. ## Re: Calculating exact time in Years, MOnths, Days

2/29/2004 4/1/2004
2/29/2004 4/2/2004
2/29/2004 4/27/2004
2/29/2004 5/2/2004

negative days

18. ## Re: Calculating exact time in Years, MOnths, Days

On your birthday are you x years old or are you x years, 1 day old?

19. ## Re: Calculating exact time in Years, MOnths, Days

Datediff function excerpt from MSDN (http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx)

Larger Intervals. If Interval is set to DateInterval.Year, the return value is calculated purely from the year parts of Date1 and Date2. Similarly, the return value for DateInterval.Month is calculated purely from the year and month parts of the arguments, and for DateInterval.Quarter from the quarters containing the two dates.

For example, when comparing December 31 to January 1 of the following year, DateDiff returns 1 for DateInterval.Year, DateInterval.Quarter, or DateInterval.Month, even though at most only one day has elapsed.

20. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by dbasnett
On your birthday are you x years old or are you x years, 1 day old?
on my next birthday i'll be x years old, but it'll be the first day of my (x + 1) year

21. ## Re: Calculating exact time in Years, MOnths, Days

Help please!!!!... My task is to make a VBA program in excel that will give the exact day on a date. For example, if the date is Feb. 1, 2011, the result must be 32, if the date is december 31, 2011, then the result must be 366. here is my code and it seems that it is not running,

Function DayOfYear(d as Integer)
Dim x as date
x=Range("A2")
d=month(x)
End Function

Sun mainDate()
Dim y as integer
MsgBox DayOFYEar(y)
End Sub

22. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by rj_yan31
Help please!!!!... My task is to make a VBA program in excel that will give the exact day on a date. For example, if the date is Feb. 1, 2011, the result must be 32, if the date is december 31, 2011, then the result must be 366. here is my code and it seems that it is not running,

Function DayOfYear(d as Integer)
Dim x as date
x=Range("A2")
d=month(x)
End Function

Sun mainDate()
Dim y as integer
MsgBox DayOFYEar(y)
End Sub

VBA question should be asked here:

http://www.vbforums.com/forumdisplay.php?f=37

23. ## Re: Calculating exact time in Years, MOnths, Days

Jeez let's not bring DateDiff into the equation. That function reeks of poor specification. "If you call it this way the return value is one less than the correct value, this is OK because we documented it." "Call it this way and we account for fractional values, call it this other way and simple math is used so you have to do your own fractional value detection." I'd rather calculate the values by spreading tea leaves in the bottom of a mug.

We've got two reasonable solutions that are guaranteed to work for leap years, differing dates, etc. because they rely on heavily-tested framework classes. Anything that involves manual calculations is less likely to be correct, and in a professional code base would increase the testing burden. I suppose that's what .paul. is attempting to prove in a roundabout manner: writing it yourself is madness.

24. ## Re: Calculating exact time in Years, MOnths, Days

and yet .paul. DID use the datediff function. Personally, I'd base the calculations using a TimeSpan object... it givesDays (and TotalDays) ... but doesn't give Months or Years as an option (really?) but between Days and TotalDays, it shouldn't be too hard to figure out the rest.

-tg

25. ## Re: Calculating exact time in Years, MOnths, Days

ok here's my complete working version (.dll + test app.):

vb Code:
`Public Class calculate     Public Structure interval        Public startDate As Date        Public endDate As Date        Public years As Integer        Public months As Integer        Public days As Integer    End Structure     Public Shared Sub calculateTime(ByRef timeInterval As interval)         Dim ed As Date = timeInterval.endDate        Dim sd As Date = timeInterval.startDate         Dim totalDays As Integer = CInt((ed - sd).TotalDays)         Dim totalMonths As Integer = countMonths(totalDays, sd)                Dim monthDaySum As Integer = (Enumerable.Range(0, totalMonths).Select(Function(i) _                                        Date.DaysInMonth(sd.AddMonths(i).Year, sd.AddMonths(i).Month))).Sum         If sd.Day > Date.DaysInMonth(sd.AddMonths(totalMonths).Year, sd.AddMonths(totalMonths).Month) Then            monthDaySum -= (sd.Day - Date.DaysInMonth(sd.AddMonths(totalMonths).Year, sd.AddMonths(totalMonths).Month))        End If         If monthDaySum > 0 Then totalDays -= monthDaySum         Dim totalYears As Integer = totalMonths \ 12        totalMonths = totalMonths Mod 12         timeInterval.years = totalYears        timeInterval.months = totalMonths        timeInterval.days = totalDays     End Sub     Private Shared Function countMonths(ByVal totalDays As Integer, ByVal d1 As Date) As Integer        Dim sum As Integer = 0        Dim counter As Integer = 0        Do            sum += Date.DaysInMonth(d1.Year, d1.Month)            If sum <= totalDays Then                d1 = d1.AddMonths(1)                counter += 1            Else                Exit Do            End If        Loop        Return counter    End Function End Class`

26. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by Sitten Spynne
Jeez let's not bring DateDiff into the equation. That function reeks of poor specification. "If you call it this way the return value is one less than the correct value, this is OK because we documented it." "Call it this way and we account for fractional values, call it this other way and simple math is used so you have to do your own fractional value detection." I'd rather calculate the values by spreading tea leaves in the bottom of a mug.
Originally Posted by techgnome
and yet .paul. DID use the datediff function. Personally, I'd base the calculations using a TimeSpan object... it givesDays (and TotalDays) ... but doesn't give Months or Years as an option (really?) but between Days and TotalDays, it shouldn't be too hard to figure out the rest.

-tg
i wasn't aware how inaccurate DateDiff was until i'd tried it

Originally Posted by Sitten Spynne
We've got two reasonable solutions that are guaranteed to work for leap years, differing dates, etc. because they rely on heavily-tested framework classes. Anything that involves manual calculations is less likely to be correct, and in a professional code base would increase the testing burden. I suppose that's what .paul. is attempting to prove in a roundabout manner: writing it yourself is madness.
afraid of a little competition???

27. ## Re: Calculating exact time in Years, MOnths, Days

We agree on this
0 years, 0 months, 1 days between 2/29/2004 3/1/2004
0 years, 11 months, 30 days between 2/29/2004 2/28/2005

But not on this
1 years, 0 months, 1 days between 2/29/2004 3/1/2005

28. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by dbasnett
We agree on this
0 years, 0 months, 1 days between 2/29/2004 3/1/2004
0 years, 11 months, 30 days between 2/29/2004 2/28/2005

But not on this
1 years, 0 months, 1 days between 2/29/2004 3/1/2005
2/29/2004 to 3/1/2005 is 1 years, 0 months, 1 days

edit: this version is extensively tested + robust unlike my previous attempts

29. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by .paul.
2/29/2004 to 3/1/2005 is 1 years, 0 months, 1 days

edit: this version is extensively tested + robust unlike my previous attempts
Then why is this

1 years, 0 months, 0 days between 1/1/2010 1/1/2011

'0 years, 6 months, 30 days between 7/29/2009 2/28/2010
'0 years, 7 months, 1 days between 7/29/2009 3/1/2010

30. ## Re: Calculating exact time in Years, MOnths, Days

2/29/2004 to 3/1/2004 is 0 years, 0 months, 1 days

so why is this wrong:

2/29/2004 to 3/1/2005 = 1 years, 0 months, 1 days

???

31. ## Re: Calculating exact time in Years, MOnths, Days

should be 1 year... since there is no 29th of Feb 2005... one year from 2/29/2004 is 3/1/2005 ... leap years are funny like that.

-tg

32. ## Re: Calculating exact time in Years, MOnths, Days

ok. try this then:

vb Code:
`Dim d As Date = #2/29/2004#MsgBox(d.AddYears(1))`

33. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by .paul.
ok. try this then:

vb Code:
`Dim d As Date = #2/29/2004#MsgBox(d.AddYears(1))`
It looks like a bug, for the reasons given by techgnome, doesn't it.

34. ## Re: Calculating exact time in Years, MOnths, Days

well, bearing in mind that the leapday has already occurred + the rest of the year is standard length, why is it a bug:

Dim d As Date = #2/29/2004#

?

35. ## Re: Calculating exact time in Years, MOnths, Days

Originally Posted by .paul.
well, bearing in mind that the leapday has already occurred + the rest of the year is standard length, why is it a bug:

Dim d As Date = #2/29/2004#

?
How many days in 2004?

I get that this is not easy(as was suggested by blake). I have been screwing around with this since it was posted, and I am only close, and it all has to do with leap years.

Code:
```            Dim d As Date = #2/29/2004#
Debug.WriteLine(d.ToShortDateString)
d = #2/28/2005#
Debug.WriteLine(d.ToShortDateString)```

36. ## Re: Calculating exact time in Years, MOnths, Days

ok i've grudgingly accepted i was wrong + i think i've solved it now:

edit: still unresolved... i've had enough...for now

37. ## Re: Calculating exact time in Years, MOnths, Days

DateDiff really works... i did it..

38. ## Re: Calculating exact time in Years, MOnths, Days

try using datediff

39. ## Re: Calculating exact time in Years, MOnths, Days

Hey this can be verry easy
I added two datetimepickers and one button

Code:
```Public Class Form1
Dim day As Integer
Dim month As Integer
Dim year As Integer
Dim day1 As Integer
Dim month1 As Integer
Dim year1 As Integer

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Filter(DateTimePicker1.Value.ToShortDateString)
day1 = day
month1 = month
year1 = year
Filter(DateTimePicker2.Value.ToShortDateString)
MsgBox(day - day1 & "-" & month - month1 & "-" & year - year1)
End Sub

Public Function Filter(ByRef date1 As String)
Dim spliting As Object
spliting = Split(date1, "-")

day = spliting(0)
month = spliting(1)
year = spliting(2)
On Error GoTo 0
End Function
End Class```

40. ## Re: Calculating exact time in Years, MOnths, Days

@rj - see the previous posts for the discussion of DateDiff. The TimeSpan provides a more accurate result.

@pieter - yeah, that's not going to work... first, ignoring the splitting of the date inaccurately, let's just look at this example:
Date 1 : 7/26/2011
Date 2 : 11/20/2011

breaking it down:
Day1 = 26
Month1 = 7
Year1 = 2011

Day = 20
Month = 11
Year = 2011

you used the following format:
day - day1 & "-" & month - month1 & "-" & year - year1

which comes out like so:
20 -26 = -6 incorrect...I don't think I need to go into why.
11 - 7 = 4 arbitrarily correct, but might not be
2011 - 2011 = 0 you kind of got lucky here... but if the second date had been 2011, then like the month, it would have been arbitrarily correct, but only depending on the rest of the date.

-tg

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured