Another Date Problem
 

Looking for a formula that will allow me to enter a date in one cell between
the following ranges using month, day, and year and have the amount of days
accrued automatically enter into another cell.
Example:
January 1, 2004 - March 15, 2004, 5 days; March 16, 2004 - April 15, 2004, 4
days; April 16, 2004 - May 15, 2004, 4 days; May 16, 2004 - June 15, 2004, 3
days, etc€¦

One possible way

=VLOOKUP(A1-"01/01/04",{0,5;75,4;106,4;136,3;167,2},2)

unless you meant that a date between March 16 and April 15 would be 9 days
(5+4) if so just change
it like this

=VLOOKUP(A1-"01/01/04",{0,5;75,9;106,13;136,16;167,and so on},2)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"dnsnider" wrote in message
...
Looking for a formula that will allow me to enter a date in one cell
between
the following ranges using month, day, and year and have the amount of
days
accrued automatically enter into another cell.
Example:
January 1, 2004 - March 15, 2004, 5 days; March 16, 2004 - April 15, 2004,
4
days; April 16, 2004 - May 15, 2004, 4 days; May 16, 2004 - June 15, 2004,
3
days, etc.

Hi!

Create a 2 column table with the first column being the=20
lower boundry dates and the second column being the=20
corresponding days value:

1/1/2004 5
3/16/2004 4
5/16/2004 3

Assume you enter your date in A1 in any true Excel date=20
format. The table is in the range G1:H3.

Use this formula to return the desired days value:

=3DVLOOKUP(A1,G1:H3,2,1)

If you enter a date that is earlier than the earliest date=20
in the table you'll get a return of #N/A. Dates later than=20
the latest date in the table will default to the latest=20
date.

eg:

12/31/2003 =3D #N/A
12/31/2007 =3D 3

Biff

-----Original Message-----
Looking for a formula that will allow me to enter a date=20
in one cell between=20
the following ranges using month, day, and year and have=20
the amount of days=20
accrued automatically enter into another cell.
Example:
January 1, 2004 - March 15, 2004, 5 days; March 16, 2004 -
April 15, 2004, 4=20
days; April 16, 2004 - May 15, 2004, 4 days; May 16,=20
2004 - June 15, 2004, 3=20
days, etc=E2?=A6

.