fun with dates

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
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.


Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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.


Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Which year are you looking to determine the number of days for?

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Stormseed:

How does that return 1.00274?
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

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 :oops:

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

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
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

Board Regular
Joined
Sep 8, 2006
Messages
89
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,154
Messages
5,704,612
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top