My discovery on DATEDIF and leap year

[Epinn]

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental," "confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[David F Cox]

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[Jim Thomlinson]

Give the bottom of this a quick look see...

http://www.cpearson.com/excel/datedif.htm

 

[Epinn]

I read the link from beginning till end *before* I posted.

Just felt like making a comment. That's all.

Epinn

Give the bottom of this a quick look see...

http://www.cpearson.com/excel/datedif.htm

 

[Epinn]

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[Biff]

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[Epinn]

Thank you Biff.

I understand what a "computer geek" is and I take it as a compliment.

I missed the "date" part. Let me tell you this. In the past, I didn't even say DATEDIF correctly. I thought it was DATED-IF. One day, I realized its role, and from then I knew it should be

DATE-DIF(ference)

Now, it is easy for you to understand how I missed "date" as in "dating." I did tell you sometimes I have a unique way of interpreting things.

I am surprised about one thing. The poster doesn't frequent this board; at least the name is not familiar to me. Looks like his post in my thread is the only post??? Sometimes, I am concerned about the kind of attention that I am drawing.

Epinn

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[Biff]

Sometimes, I am concerned about the kind of attention that I am drawing.

Well, it's better than getting flamed by some of the idiots in other places,
isn't it?

Ask all the questions you like. How else will you learn? I'm not an expert
but I do know a thing or two about Excel and most of what I know I learned
right here. Invest some time here and before you know it you'll be answering
the questions rather than asking the questions.

Biff

Thank you Biff.

I understand what a "computer geek" is and I take it as a compliment.

I missed the "date" part. Let me tell you this. In the past, I didn't even
say DATEDIF correctly. I thought it was DATED-IF. One day, I realized its
role, and from then I knew it should be

DATE-DIF(ference)

Now, it is easy for you to understand how I missed "date" as in "dating." I
did tell you sometimes I have a unique way of interpreting things.

I am surprised about one thing. The poster doesn't frequent this board; at
least the name is not familiar to me. Looks like his post in my thread is
the only post??? Sometimes, I am concerned about the kind of attention that
I am drawing.

Epinn

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[JMB]

Although using Datedif on the following
2/1/2008
1/31/2010

yields 365, but would give #NUM! if Date2 was 1/31/2008 (as Chip stated). I
think XL help is misleading when it says the years are ignored - because they
are obviously not completely ignored.

 

[Epinn]

Biff, thank you for your support and encouragement, as always. You have no idea how much the support and advice from everyone in this forum mean to me.

Tell me about it. I was once harassed by a "stalker" who frequents the MS forums. He complained about me asking questions in *this* forum which he doesn't have to support. Wish my stalker can read this "Ask all the questions you like." He just read the *header* of a few of my posts and thought I was lazy and didn't use Excel Help. For those who know me, Excel Help, Google and the MVP links are my first line of support. Now you understand what I meant by drawing attention.

Oh, you know more than "a thing or two." I can't figure out how some people can be this smart.

Epinn

Sometimes, I am concerned about the kind of attention that I am drawing.

Well, it's better than getting flamed by some of the idiots in other places,
isn't it?

Ask all the questions you like. How else will you learn? I'm not an expert
but I do know a thing or two about Excel and most of what I know I learned
right here. Invest some time here and before you know it you'll be answering
the questions rather than asking the questions.

Biff

Thank you Biff.

I understand what a "computer geek" is and I take it as a compliment.

I missed the "date" part. Let me tell you this. In the past, I didn't even
say DATEDIF correctly. I thought it was DATED-IF. One day, I realized its
role, and from then I knew it should be

DATE-DIF(ference)

Now, it is easy for you to understand how I missed "date" as in "dating." I
did tell you sometimes I have a unique way of interpreting things.

I am surprised about one thing. The poster doesn't frequent this board; at
least the name is not familiar to me. Looks like his post in my thread is
the only post??? Sometimes, I am concerned about the kind of attention that
I am drawing.

Epinn

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[David F Cox]

I do frequent this board, as an avid reader,desperately trying to get my
Excel skills up-to-date, and learning lots. I am running Office 2007 Beta.
Sorry about my mis-guided attempts at humour.

David F. Cox


Thank you Biff.

I understand what a "computer geek" is and I take it as a compliment.

I missed the "date" part. Let me tell you this. In the past, I didn't even
say DATEDIF correctly. I thought it was DATED-IF. One day, I realized its
role, and from then I knew it should be

DATE-DIF(ference)

Now, it is easy for you to understand how I missed "date" as in "dating." I
did tell you sometimes I have a unique way of interpreting things.

I am surprised about one thing. The poster doesn't frequent this board; at
least the name is not familiar to me. Looks like his post in my thread is
the only post??? Sometimes, I am concerned about the kind of attention that
I am drawing.

Epinn

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[Epinn]

JMB,

Thank you for your discovery.

Looks like Excel takes "year" but just *ONE* into consideration when the second argument (month and day) is less than the first.

I took your experiment further and played with 2/1/2007 and 1/31/2009. It yields 364. Therefore, I think the leap year is taken into consideration. The following comment in Chip's writeup still holds true.

You wrote: "I think XL help is misleading when it says the years are ignored."

Maybe this is why DATEDIF is no longer found using F1 after version 2000. Anyone knows if DATEDIF still exists in 2007?

Epinn

Although using Datedif on the following
2/1/2008
1/31/2010

yields 365, but would give #NUM! if Date2 was 1/31/2008 (as Chip stated). I
think XL help is misleading when it says the years are ignored - because they
are obviously not completely ignored.

 

[Epinn]

Sorry about my mis-guided attempts at humour.

No harm done. I was wondering if you were my "stalker" in disguise. Details in another post under this thread.

I want to apologize to others for the distraction.

Epinn

I do frequent this board, as an avid reader,desperately trying to get my
Excel skills up-to-date, and learning lots. I am running Office 2007 Beta.
Sorry about my mis-guided attempts at humour.

David F. Cox


Thank you Biff.

I understand what a "computer geek" is and I take it as a compliment.

I missed the "date" part. Let me tell you this. In the past, I didn't even
say DATEDIF correctly. I thought it was DATED-IF. One day, I realized its
role, and from then I knew it should be

DATE-DIF(ference)

Now, it is easy for you to understand how I missed "date" as in "dating." I
did tell you sometimes I have a unique way of interpreting things.

I am surprised about one thing. The poster doesn't frequent this board; at
least the name is not familiar to me. Looks like his post in my thread is
the only post??? Sometimes, I am concerned about the kind of attention that
I am drawing.

Epinn

Translation:

The observation your posted is one that only a true "computer geek" could
appreciate. Geeks are known to be nerds who have trouble getting "dates"
with the ladies/men. In this case, "dates" is a play on DATEDIF.

Biff

Don't understand what you are trying to say. Can you rephrase that please?

Epinn

Not for nothing do geeks have a reputation for having problems with dates

My discovery on DATEDIF ("yd") and leap year

A1:A4

2/1/2007
3/1/2008
2/1/2008
3/1/2009


formula (1) =DATEDIF(A1,A2,"yd") yields 28

formula (2) =DATEDIF(A3,A4,"yd") yields 29

I understand why this is happening - the year for the first argument is
used.

I find DATEDIF and "yd" and leap year "interesting," "temperamental,"
"confusing," ...... <g>

I am curious if "yd" is used often.

Welcome comments to this and my discovery.

Epinn

 

[David F Cox]

typing DATEDIF into 2007 Beta help gets you a nice pop-up that explains how
to use various other functions to achieve standard date calculations. It
makes no mention of DATEDIF. However typing your examples in gives the same
results, so backward compatability seems to rule.

JMB,

Thank you for your discovery.

Looks like Excel takes "year" but just *ONE* into consideration when the
second argument (month and day) is less than the first.

I took your experiment further and played with 2/1/2007 and 1/31/2009. It
yields 364. Therefore, I think the leap year is taken into consideration.
The following comment in Chip's writeup still holds true.

You wrote: "I think XL help is misleading when it says the years are
ignored."

Maybe this is why DATEDIF is no longer found using F1 after version 2000.
Anyone knows if DATEDIF still exists in 2007?

Epinn

Although using Datedif on the following
2/1/2008
1/31/2010

yields 365, but would give #NUM! if Date2 was 1/31/2008 (as Chip stated). I
think XL help is misleading when it says the years are ignored - because
they
are obviously not completely ignored.

 

[Epinn]

...... misleading when it says the years are ignored - because they
are obviously not completely ignored.<<

I have found more evidence....... When we specify "y" the month is actually taken into consideration as well.

A1:A3

12/25/1923
11/11/2006
10/25/1923


=DATEDIF(A1,A2,"y") yields 82
=YEAR(A2)-YEAR(A1) yields 83

=DATEDIF(A3,A2,"y") yields 83
=YEAR(A2)-YEAR(A3) yields 83

I guess depending on the users' need, sometimes we may want to use YEAR( ) while other times DATEDIF( ). But for calculating age, I will definitely go with DATEDIF( ).

Epinn

Although using Datedif on the following
2/1/2008
1/31/2010

yields 365, but would give #NUM! if Date2 was 1/31/2008 (as Chip stated). I
think XL help is misleading when it says the years are ignored - because they
are obviously not completely ignored.