# fun with dates

#### jjyxk845

##### Board Regular
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Stormseed

##### Banned
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
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
Stormseed:

How does that return 1.00274?

#### Stormseed

##### Banned

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

##### Board Regular
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

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

#### jjyxk845

##### Board Regular
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
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
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.

Replies
3
Views
83
Replies
7
Views
204
Replies
4
Views
165
Replies
6
Views
88
Replies
3
Views
57