Help With DateDiff

[Evagrius Ponticus]

Hi,

I am not sure if DateDiff can do this. The code below give me 7. But I want the answer to include only full months - not partial months. I know that can be done in Excel using the Datedif, but not sure if it is possible using the VB Datediff.

So I would like 6 to be the answer below because there are only 6 full month between the two dates.


cells(MyRow, "s") = 8/31/2013

cells(15,Mycol) = 3/2/2014

 Cells(MyRow, MyCol).Value = DateDiff("d", Cells(MyRow, "S"), Cells(15, MyCol))

 

[Evagrius Ponticus]

Emma - I used the updated version and it is correct. I don't think I'll make any changes to it though unless you think it is necessary. Maybe once I understand the structure better I can condensce it. Thanks,

 

[cornflakegirl]

No, the change isn't necessary - the code just wasn't as neat as it should be.

 

[Boller]

Black Tiger

It depends what you mean by "full months".

For example, with a start date of 12/31/2007 and an end date of 2/29/2008 the code will return 29 days, but there are exactly 2 complete calendar months between the two dates.

 

[cornflakegirl]

Boller - imho, that's a somewhat strange definition of 2 months. Your logic is irrefutable, but I think that the code returns what I would expect to see for those dates.

However, it is of course entirely down to what Black Tiger wants to see

 

[theozz]

How aboiut this...

Sub x()
Dim d, a, b
a = Range("e1").Value
b = Range("f1").Value
     
     If Day(a) > Day(b) Then
          d = DateDiff("m", a, b) - 1
     Else
          d = DateDiff("m", a, b)
     End If
     
     Range("d1") = d
End Sub

 

[Boller]

Boller - imho, that's a somewhat strange definition of 2 months. Your logic is irrefutable, but I think that the code returns what I would expect to see for those dates.

However, it is of course entirely down to what Black Tiger wants to see

Why would you think it strange?

As I wrote in my post, it is 2 complete calendar months.

Would it not also be strange to descibe it as 1 month 29 days?

Also, if the end date is only 1 day later (3/1/08 instead of 2/29/08), the result would go from 1 month 29 days to 2 months 1 day.
To where did the exact 2 months difference disappear?

 

[Boller]

theozz

Doesn't your code do the same thing as the code previously posted?

 

[cornflakegirl]

Boller - I'm not arguing that it isn't 2 months - as I said, your logic is irrefutable. I guess my original response was badly worded (too late at night) - it's not so much that I think that it would be strange to return an answer of two months - more that I think it's strange to require that to be the answer. I think that it's reasonable to only return an exact month answer when the day numbers are the same.

But as I said before, it's entirely dependent on what BlackTiger wants, so I think we can safely agree to disagree until he responds! If you want to produce some code that deals with your objection, feel free

 

[Boller]

"I think that it's reasonable to only return an exact month answer when the day numbers are the same."

That's a somewhat strange definition. Your logic is refutable.

In fact, I think it's unreasonable.

However, when calculating date differences, there is no absolute method.
It depends solely upon user requirements.

I can think of many instances where the return of complete calendar months is needed (rather than your definition of complete months).
For example, where a pension entitlement, or leave entitlement, etc. are based upon the number of complete months.