Another Date Problem

[dnsnider]

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…

 

[Peo Sjoblom]

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)

 

[Biff]

Hi!

Create a 2 column table with the first column being the
lower boundry dates and the second column being the
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
format. The table is in the range G1:H3.

Use this formula to return the desired days value:

=VLOOKUP(A1,G1:H3,2,1)

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

eg:

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

Biff