Counting Days

[Belinda7237]

I thought my formula was working but not accurate:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-P1

where P1 has a due date in it.

I am trying to calculate based on P1 how many days an item will be projected
past due at month end.

So if P1 had 5/15/2008 in it, and I ran the report today in June at June
month end it would be 45 days past due, and if its past due when i run again
in July the value would be 76 days past due.

What am i doing wrong?

Thanks!

 

[David Biddulph]

You've miscounted the number of days. 15 May to 15 June is 31 days, so 15
May to 30 June (last day of this month) is 46 days, not 45.
The formula correctly returns 46.
If you want 45, you can, of course, subtract 1.

 

[Belinda7237]

I am getting a #Value error?

You've miscounted the number of days. 15 May to 15 June is 31 days, so 15
May to 30 June (last day of this month) is 46 days, not 45.
The formula correctly returns 46.
If you want 45, you can, of course, subtract 1.

 

[David Biddulph]

That's what you'll get if there isn't a real date in P1. What does
=ISNUMBER(P1) show you?
Does what you see in P1 change if you change the cell formatting, for
example to 15 May 2008?
If the displayed value doesn't change, then you've probably got text in the
cell, rather than a real date.
To tackle the simplest question first, I get #VALUE! if I put 5/15/2008
(instead of 15/5/2008) in P1, because my Windows Regional Settings are
looking for d/m/y, not m/d/y; are you sure that your data is matching your
Windows settings?

 

[Belinda7237]

so when i put in =isnumber(p1) i got False.
my dates are
m/d/y and not d/m/y therefore should i write the formula differently?
sorry I am a true novice!

 

[David Biddulph]

Try deleting the content of the P1 and retyping 5/15/2008, or better still
15 May 2008 which is unambiguous.
Otherwise, look carefully in the formula bar to see what you've got in P1.
It is obviously text rather than the real date. You may have spaces or
other non-printing characters, perhaps?

 

[Belinda7237]

thank you so much for your patience - i realized that my P1 should actually
be P2 because i have headings in my columns! after making that correction it
works perfectly. Thanks a million!