fun with dates

[jjyxk845]

Hi,

I want to determine the number of days between to dates in dates.

Heres the code I've got The problem I've got is with leap years

Heres the sample data

Earlier Date Later Dater output
29/02/2004 28/02/2005 1.00000 This is the wrong one
28/02/2005 28/02/2006 1.00274
29/02/2004 28/02/2005 1.00274
28/02/2005 28/02/2006 1.00274

What it should return is 1.00274 like the rest.

Function ex(LaterDate As Variant, EarlierDate As Variant)

Dim y(1) As Integer
Dim d(1) As Double
Dim years As Integer
Dim daysinyear As Integer
Dim days As Integer


    If IsDate(LaterDate) And IsDate(EarlierDate) Then
    
        If EarlierDate <= LaterDate Then
            
            y(0) = Year(EarlierDate)
            y(1) = Year(LaterDate)
            
            years = y(1) - y(0)
            
            
            daysinyear = (DateSerial(y(1) + 1, Month(EarlierDate), Day(EarlierDate)) - DateSerial(y(1), Month(EarlierDate), Day(EarlierDate)))
            dt = DateSerial(y(1), Month(EarlierDate), Day(EarlierDate))
            If dt > LaterDate Then
                dt = DateSerial(y(1) - 1, Month(EarlierDate), Day(EarlierDate))
                daysinyear = (DateSerial(y(1), Month(EarlierDate), Day(EarlierDate)) - DateSerial(y(1) - 1, Month(EarlierDate), Day(EarlierDate)))
                years = years - 1
            End If
            
            days = LaterDate - dt + 1
            
            ex = years + days / daysinyear
        
        Else
            ex = 0
        End If
    Else
        ex = CVErr(xlErrNA)
    
    End If

End Function

 

[Stormseed]

Hi,

I want to determine the number of days between to dates in dates.

Heres the code I've got The problem I've got is with leap years

Heres the sample data

Earlier Date Later Dater output
29/02/2004 28/02/2005 1.00000 This is the wrong one
28/02/2005 28/02/2006 1.00274
29/02/2004 28/02/2005 1.00274
28/02/2005 28/02/2006 1.00274

What it should return is 1.00274 like the rest.

Function ex(LaterDate As Variant, EarlierDate As Variant)

Dim y(1) As Integer
Dim d(1) As Double
Dim years As Integer
Dim daysinyear As Integer
Dim days As Integer


    If IsDate(LaterDate) And IsDate(EarlierDate) Then
    
        If EarlierDate <= LaterDate Then
            
            y(0) = Year(EarlierDate)
            y(1) = Year(LaterDate)
            
            years = y(1) - y(0)
            
            
            daysinyear = (DateSerial(y(1) + 1, Month(EarlierDate), Day(EarlierDate)) - DateSerial(y(1), Month(EarlierDate), Day(EarlierDate)))
            dt = DateSerial(y(1), Month(EarlierDate), Day(EarlierDate))
            If dt > LaterDate Then
                dt = DateSerial(y(1) - 1, Month(EarlierDate), Day(EarlierDate))
                daysinyear = (DateSerial(y(1), Month(EarlierDate), Day(EarlierDate)) - DateSerial(y(1) - 1, Month(EarlierDate), Day(EarlierDate)))
                years = years - 1
            End If
            
            days = LaterDate - dt + 1
            
            ex = years + days / daysinyear
        
        Else
            ex = 0
        End If
    Else
        ex = CVErr(xlErrNA)
    
    End If

End Function

Why dont use a formula instead !

check it out...

Birth date : 1-Jan-60

Years lived : 46 =DATEDIF(C8,TODAY(),"y")
and the months : 9 =DATEDIF(C8,TODAY(),"ym")
and the days : 8 =DATEDIF(C8,TODAY(),"md")

Also you can put this stuff all together into one calculation:

Age is 46 Years, 9 Months and 8 Days
="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

 

[Scott Huish]

Which year are you looking to determine the number of days for?

In your first example, is it 2004, 2005 or 2006?

 

[Scott Huish]

Stormseed:

How does that return 1.00274?

 

[Stormseed]

Stormseed:

How does that return 1.00274?

Well...it will not....just posted something related to a formula with a completely different example...which he can utilize for his stuff...watever....he needs to do with dates

I did not create a nuisance though....I reckon !!

 

[jjyxk845]

Right The function returns the number of complete years and days .

29/02/2004 to 28/02/2005 is 1 complete year and 1 day therefore out put will be 1.00274

because 1/365 =0.00274

So but because of the leap year its only calculating it as 1 year.

The other examples all work correcttly returning the correct values.

 

[Scott Huish]

But what year are you trying to determine the number of days for? The earlier date, the later date or what?

 

[jjyxk845]

its the difference between the dates.

Ie 31st jan 2001 to 31st jane 2002 is a year and a day inclusive.

using the DATEDIF function below it returns the following

DATEDIF("29/02/2004","28/02/2005", "y") = 0

even though it should be a complete year as its the last day in Feb till the last day in feb.

if you try the same example for Jan

DATEDIF("31/01/2004","31/01/2005", "y") = 1

Which is correct!

 

[jjyxk845]

right I've had a play around In VBA you have to use Datediff which if you return the number of days between

29/02/2004 and 28/02/2005 gives 365
31/01/2004 and 31/01/2005 gives 366
31/01/2003 and 31/01/2004 gives 365

which I think I can work with but if anyone can give a hand that would be great.

 

[Scott Huish]

First, 0.00274 is rounded to 5 decimal places, Excel actually takes it 0.00273972602739726

and your first answer should not be the same as the rest, because they are in two different years, one of which is a leap year and one isn't.
So, the first 307 days need to be divided by 366 and the next 59 days need to be divided by 365, and summed which is a result of 1.00044164982409

The same situation would apply if they were both in the same year.
If the first date was 2/29/04 and the second date 3/1/04, this would be 2/366 but 2/28/05 and 3/1/05 would be 2/365, same number of days but a different answer.

Hopefully, someone will be able to help you out, but I just thought that I would point this out.