PDA

View Full Version : Solved: Datedif International



Wolfgang
11-21-2006, 05:31 AM
Hi All...

we are having a lenghty discussion about said function as far as it may be used in a date calculation like that:

A1 = 15.04.2003
A2 = 14.02.2007

The result should be shown as months...

Everybody tells me to use Datedif and I keep telling them that this kind of function only works in the US-community but not here...

Who is right, please?!

Best,
Wolfgang

Bob Phillips
11-21-2006, 06:03 AM
Wolfgang,

I don't have a German version of Excel available to test, but my guess would be that everybody is right, you are wrong <G>.

Datedif has a Unit argument which is text and this would give a problem in foreign language versions of Excel, as German Excel does not recognize Y for year. But if you translate, it should be fine. So use J for year, T for day, but months should just still be M.

Wolfgang
11-21-2006, 06:13 AM
Hi Bob...
Thank you very much for your quick answer...BUT I am not convinced (yet) that I am wrong...

The statement below is taken from earlier discussions with US-natives and therefore I believe I can't use Datedif for the above calculation...

Start Quote: This is one of those "it's not a bug, it's a feature" situations. The Help topic for the DateDiff function says "When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed." This is not what you (or most people, I think) would want, so to get a return value of the number of full years elapsed between two dates, you need a more complex function...End Quote.

And what about the Leap Year?

Best,
Wolfgang

BTW: What would be the result of YOUR calculation, please?!

Best,
Wolfgang

Charlize
11-21-2006, 06:14 AM
Make a function of your own is another way to go. In vba you've got to use the international standard (english ?)
Function h_m_m(var1 As Date, var2 As Date)
'function how many months
'var1 is starting date
'var2 is ending date
h_m_m = DateDiff("m", var1, var2)
End Function
This will give months between two dates.

Charlize

Wolfgang
11-21-2006, 06:30 AM
Hi Charlize...

Learning that you are from Belgium leads me into believing your solution...

Our comedians produce results between 45 and 48 months...

Me has no clue because about the above mentioned Help-statement...

Thank you very much..

Best,
Wolfgang

Wolfgang
11-21-2006, 06:45 AM
Hi All...

I calculated 1401 days...which is close enough for governments work...

Case closed...

Thank you both...

Best,
Wolfgang

Bartek
11-21-2006, 06:48 AM
Hi,


Everybody tells me to use Datedif and I keep telling them that this kind of function only works in the US-community but not here...

Well, not exactly becasue conversion routines use regional settings. I am using Polish version of Excel, and DateDiff works with Continental date format like:


DateDiff("m", "15-04-2003", "14-02-2007")

Or even strictly Polish format:


DateDiff("m", "15 kwi", "14 lis")

I get Type Mismatch Error if I try to use English format like:


DateDiff("m", "Apr 15", "Nov 17")

But what is funny, this English Format works correctly:


DateDiff("m", "04-15-2003", "02-14-2007")

Charlize
11-21-2006, 07:52 AM
What about this one
Function h_m_year(var1 As Date, var2 As Date)
'function for the number of years
'var1 is starting date
'var2 is ending date
Dim h_m_m 'how many months
Dim h_m_year2 'help variable
h_m_m = DateDiff("m", var1, var2)
h_m_year2 = h_m_m / 12
h_m_year = Split(h_m_year2, ".")
End Function
Charlize

Wolfgang
11-21-2006, 08:20 AM
Hi Charlize...
the only things missing are WEEKS and DAYS...

With these additional toys I will be able to rock my comedians...

Thank you very much for your time...

Best,
Wolfgang