Update Upon Opening based on DATEIF NOW()

[sherry]

Hi All,

If I enter, for example, a birth date in column A1 and the below formula in
another column, I understand the result will be someone's age in years,
months, and days. My question is: Tomorrow, when I open my file, will my
result show one more day added to the "days" number? Thanks for your time.

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

 

[JE McGimpsey]

Well, kinda...

DATEDIF() uses the length of the first month to determine month lengths,
so that leads to some bizarre results:

Assume A1 = 1/31/2007

then some results will look like:

2/27/2009 2 years, 0 months, 27 days
2/28/2009 2 years, 0 months, 28 days
3/1/2009 2 years, 1 months, -2 days
3/2/2009 2 years, 1 months, -1 days
3/3/2009 2 years, 1 months, 0 days
3/4/2009 2 years, 1 months, 1 days
3/5/2009 2 years, 1 months, 2 days

likewise

4/29/2009 2 years, 2 months, 29 days
4/30/2009 2 years, 2 months, 30 days
5/1/2009 2 years, 3 months, 0 days
5/2/2009 2 years, 3 months, 1 days
5/3/2009 2 years, 3 months, 2 days

 

[FSt1]

hi
yes it will. but i would substitute Today() for Now() in the formula since
now() incluces time also. the datediff() formula will strrip the time off but
using today() would just be less clutter excel has to trip over.

my thoughts.
Regards
FSt1

 

[sherry]

Good point - I'll change it right now. thanks!!

 

[sherry]

So whatever month is in the A1 cell is telling my whole DATEIF() formula how
many days to use to count my days in a month?? It seems to me that it would
change each time I pasted the formula in a different cell next to a different
date. I guess I'm missing something. Can you offer advice on a better
formula. Any help is greatly appreciated.

However, right before I hit "post", I just checked my results. None were
wrong. I put in your dates and did not get the results you got. I'm using
Excel 2007. Maybe you're speaking of a different version and this one
actually works right!!

 

[JE McGimpsey]

Ah, it appears that XL07 has changed DATEDIF(), as I get expected
results for the examples I gave.

However, as I wrote before, it is NOT possible to develop a *general and
consistent* formula. It's an inherent problem in the fuzzy definition of
"months".

With XL07, MS has (apparently) decided to shift the point where the
error occurs. Why they'd do that, I don't know, But that doesn't make it
right. If anything, in many ways, it's WORSE.

In XL07, set 1/31/2007 as your base date, then:

1/30/2009 1 years, 11 months, 30 days
1/31/2009 2 years, 0 months, 0 days
2/1/2009 2 years, 0 months, -2 days
2/2/2009 2 years, 0 months, -1 days
2/3/2009 2 years, 0 months, 0 days
2/4/2009 2 years, 0 months, 1 days

So now by advancing one day (1/31 -> 2/1) the result actually appears to
lose 2 days!

Or, use 2/28/2007 as your base date:

2/27/2009 1 years, 11 months, 27 days
2/28/2009 2 years, 0 months, 0 days
3/1/2009 2 years, 0 months, 4 days
3/2/2009 2 years, 0 months, 5 days


Note the jump from 2Y 0M 0D, to 2Y 0M 4D when advancing only one actual
day...

If you can live with that, that's OK, of course.

 

[sherry]

First of all, you don't need to actually "type in" a base date anywhere. You
just use the date you want to use. In my case, they are birth dates. Type
in your birthdate in A1. Type in the formula in B1 and it will show you how
old you are in years, months and days. That's all I need to show.

Secondly, I will never have the problem you're describing (for this task
anyway) because the examples that are showing errors are future dates. Once
again, I am using birth dates. You need to be born before I can enter your
date so I will not be putting in future dates!

 

[sherry]

sorry - posted to the wrong member:

First of all, you don't need to actually "type in" a base date anywhere. You
just use the date you want to use. In my case, they are birth dates. Type
in your birthdate in A1. Type in the formula in B1 and it will show you how
old you are in years, months and days. That's all I need to show.

Secondly, I will never have the problem you're describing (for this task
anyway) because the examples that are showing errors are future dates. Once
again, I am using birth dates. You need to be born before I can enter your
date so I will not be putting in future dates!

 

[JE McGimpsey]

Again, if you're OK with that, that's fine.

But, your argument about birth dates vs. future dates doesn't make any
sense to me whatsoever!

Let's say you have an already-born Ms. Jones recorded as having come
into the world on 31 January 1965.

Today (17 January 2009), when you check her age, you'll find that XL
(I'm assuming XL07) believes Ms. Jones to be

43 years, 11 months, 17 days

old. And most people would probably agree.

If today's the last day you're going to use that spreadsheet, you're
good to go and I've wasted my time with what comes next.

However, let's say you want to check your spreadsheet again on Monday, 2
February 2009. When you fire it up, you'll find that XL thinks that Ms.
Jones is now

44 years, 0 months, -1 days

old. Hmm... a day less than 44 years?

For MOST people, that doesn't make much sense - how can one be less than
44 years old two days after one turned 44?

When, a couple months later, you fire up XL on Tuesday, 31 March 2009,
XL will tell you that Ms. Jones is now the ripe old age of

44 years, 2 months, 0 days

old. Again, most people would probably agree.

But when you check again the next day (1 April), XL will tell you that
Ms. Jones is STILL

44 years, 2 months, 0 days

old. April Fools!

Once again, for MOST people, this behavior is not desirable. For MOST
people, the age of someone shouldn't be reported this way (but exactly
*how* it should be reported varies from situation to situation).

I'm only trying to point out a potential problem.

Since there is NO way to consistently and generally solve this problem,
you're either going to have to live with it (which is FINE), or you're
going to have to specify what you expect to happen around month
boundaries and craft a consistent (but not general) formula.

 

[sherry]

Thank you so much for all the time you took responding to my issue. Have a
great day!

 

[sherry]

Thank you so much for all the time you took responding to my issue. Have a
great day!