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?
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.
Let’s start with some easy ones:
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#
Do While aDate <= endDate
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))
aDate = aDate.AddDays(1)
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.nextMonth = Me.Anniversary.AddOneMonth
Me.checkNextMonth()
Do While Me.nextBDay < Me.Age.DateFuture
Me.DayPlus1()
Me.nextBDay = Me.nextBDay.AddDays(1)
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
If Me.Anniversary.AddMonths(Me.Age.Months).AddDays(Me.Age.Days) >= Me.nextMonth Then
Me.MonthPlus1()
End If
End Sub
Private Sub MonthPlus1()
Me.Age.Days = 0
Me.Age.Months += 1
Me.nextMonth = Me.Anniversary.AddMonths(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
'it is, adjust nextBDay
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
Last edited by dbasnett; Oct 28th, 2011 at 11:15 AM.
Well, if you have .77 of a month, that would mean you have 77% 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.
*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
currentDate = currentDate.AddDays(1)
' 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.
Last edited by Sitten Spynne; Jul 27th, 2011 at 09:40 AM.
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.
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.
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
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
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.
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.
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.
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#
MsgBox(d.AddDays(365))
?
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#
d = d.AddDays(366)
Debug.WriteLine(d.ToShortDateString)
d = #2/28/2005#
d = d.AddDays(365)
Debug.WriteLine(d.ToShortDateString)
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
@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
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.