My discovery on DATEDIF and leap year
 

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

My discovery on DATEDIF and leap year
 

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

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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

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

Jim Thomlinson


"Epinn" wrote:

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

My discovery on DATEDIF and leap year
 

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

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

Epinn

"Jim Thomlinson" wrote in message ...
Give the bottom of this a quick look see...

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

Jim Thomlinson


"Epinn" wrote:

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

My discovery on DATEDIF and leap year
 

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

Epinn

"David F Cox" wrote in message ...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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

"Biff" wrote in message ...
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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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

"Epinn" wrote in message
...
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

"Biff" wrote in message
...
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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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.



"Jim Thomlinson" wrote:

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

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

Jim Thomlinson


"Epinn" wrote:

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

My discovery on DATEDIF and leap year
 

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.

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

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

"Biff" wrote in message ...
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

"Epinn" wrote in message
...
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

"Biff" wrote in message
...
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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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


"Epinn" wrote in message
...
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

"Biff" wrote in message
...
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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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.

Excel will use the year of the first date......to determine whether to include 29-February in the calculation.

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

"JMB" wrote in message ...
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.



"Jim Thomlinson" wrote:

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

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

Jim Thomlinson


"Epinn" wrote:

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

My discovery on DATEDIF and leap year
 

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

"David F Cox" wrote in message ...
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


"Epinn" wrote in message
...
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

"Biff" wrote in message
...
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

"Epinn" wrote in message
...
Don't understand what you are trying to say. Can you rephrase that please?

Epinn

"David F Cox" wrote in message
...
Not for nothing do geeks have a reputation for having problems with dates :)

"Epinn" wrote in message
...
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

My discovery on DATEDIF and leap year
 

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.

"Epinn" wrote in message
...
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.

Excel will use the year of the first date......to determine whether to
include 29-February in the calculation.

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

"JMB" wrote in message
...
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.



"Jim Thomlinson" wrote:

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

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

Jim Thomlinson


"Epinn" wrote:

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

My discovery on DATEDIF and leap year
 

...... 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

"JMB" wrote in message ...
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.



"Jim Thomlinson" wrote:

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

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

Jim Thomlinson


"Epinn" wrote:

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