2 Que's regarding dates (matching and subtracting)

[jacob farino]

Thanks for the help!
Okay, I got que. #1 all figured out!

#2 however, is still a little sticky.

This is exactly what I'm trying to accomplish:

The end-user enters a date in cell a2, validated to format DD/MM/YYYY.

I want to look up that date and match it to a corresponding "table" on
sheet3.

On Sheet3, currently, I have the date, in M (September) format, in column B,
and a value (%) in column C.
Essentially, I want to input date ranges in this "table", so for column B2,
it would be September 16th, 2004, to October 15th 2004 (instead of just
September), but I don't know how. C2 would equal the value I have manually
entered for that range, 4.96%. B3 would need ranges October 16th-November
15th, 2004 with a corresponding C3 value of 5.06%.

So when the end-user enters in a date in A2, it looks up where that date
falls on Sheet3, column B and displays the corresponding value in, say, A3.

In the above example, if 10/14/2004 were entered in A2, then A3 would need
to display 4.96%.

Thank-you for your aid!

Jacob

Message 1 in thread
From: Jacob ([email protected])
Subject: 2 Que's regarding dates (matching and subtracting)


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-10-08 10:51:41 PST


Hello!

I'm trying to do the following:

In C8, users enter in a date.

In K30, I want to display the number of days until the end of the month,
using the C8 field as the start date/month.

Example: User enters 10/8/2004 in C8
K30 should equal 23.

It also needs to accomodate the calendar year 2005, and so on. Is this
possible?



2ND QUE:

The above answer may also take care of this, but what is the date format if
I want to use a subtraction or matching formula.

Example: A1 = 2/1/2004

I have a column of months (J), and a corresponding set of values (K). I want
to display the value that corresponds to the February month in A2.




Thanks so much,
JacobPost a follow-up to this message

Message 2 in thread
From: Allan S. Warrior (warriora_at_yahoodotcom)
Subject: RE: 2 Que's regarding dates (matching and subtracting)


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-10-08 12:41:13 PST


I tested with this formula; see how it works for you:
=EOMONTH(C8,0.6)-C8. Result should be formatted as number, not date.

If you can't find or use EOMONTH (ie it returns #NAME?) you need to load the
Analysis Toolpak Add-In. (Tools>Add-Ins>Analysis Toolpak). You may need
your
Office install media, and it's usually a good idea to restart Excel after
loading Add-Ins.

Hope this helps!

Hello!

I'm trying to do the following:

In C8, users enter in a date.

In K30, I want to display the number of days until the end of the month,
using the C8 field as the start date/month.

Example: User enters 10/8/2004 in C8
K30 should equal 23.

It also needs to accomodate the calendar year 2005, and so on. Is this
possible?



2ND QUE:

The above answer may also take care of this, but what is the date format
if
I want to use a subtraction or matching formula.

Example: A1 = 2/1/2004

I have a column of months (J), and a corresponding set of values (K). I
want
to display the value that corresponds to the February month in A2.




Thanks so much,
Jacob


Post a follow-up to this message

Message 3 in thread
From: Allan S. Warrior (warriora_at_yahoodotcom)
Subject: RE: 2 Que's regarding dates (matching and subtracting)


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-10-08 13:22:48 PST


minor typo (function will work either way since the argument truncates,
but...it bothers me)
=EOMONTH(C8,0)-C8

I tested with this formula; see how it works for you:
=EOMONTH(C8,0.6)-C8. Result should be formatted as number, not date.

If you can't find or use EOMONTH (ie it returns #NAME?) you need to load
the
Analysis Toolpak Add-In. (Tools>Add-Ins>Analysis Toolpak). You may need
your
Office install media, and it's usually a good idea to restart Excel after
loading Add-Ins.

Hope this helps!

Message 4 in thread
From: Allan S. Warrior (warriora_at_yahoodotcom)
Subject: RE: 2 Que's regarding dates (matching and subtracting)


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-10-08 12:52:18 PST


Question 2: I got a workable result with =VLOOKUP(MONTH(A1),J1:K12,2,FALSE),
however, only if I put in the number of the month in column J rather than
the
name of the month. (1=Jan, 2=Feb, 3=Mar, etc). Somebody else may have a
better solution.

Hello!

I'm trying to do the following:

In C8, users enter in a date.

In K30, I want to display the number of days until the end of the month,
using the C8 field as the start date/month.

Example: User enters 10/8/2004 in C8
K30 should equal 23.

It also needs to accomodate the calendar year 2005, and so on. Is this
possible?



2ND QUE:

The above answer may also take care of this, but what is the date format
if
I want to use a subtraction or matching formula.

Example: A1 = 2/1/2004

I have a column of months (J), and a corresponding set of values (K). I
want
to display the value that corresponds to the February month in A2.




Thanks so much,
Jacob


Post a follow-up to this message

Message 5 in thread
From: Robert McCurdy ([email protected])
Subject: Re: 2 Que's regarding dates (matching and subtracting)


View this article only
Newsgroups: microsoft.public.excel.misc
Date: 2004-10-10 08:55:07 PST


In case the Analysis Toolpak Add-In is not open.

=DATE(YEAR(C8),MONTH(C8)+1,0)-C8


Regards
Robert McCurdy

Hello!

I'm trying to do the following:

In C8, users enter in a date.

In K30, I want to display the number of days until the end of the month,
using the C8 field as the start date/month.

Example: User enters 10/8/2004 in C8
K30 should equal 23.

It also needs to accomodate the calendar year 2005, and so on. Is this
possible?



2ND QUE:

The above answer may also take care of this, but what is the date format if
I want to use a subtraction or matching formula.

Example: A1 = 2/1/2004

I have a column of months (J), and a corresponding set of values (K). I want
to display the value that corresponds to the February month in A2.




Thanks so much,
Jacob




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004Post a
follow-up to this message




©2004 Google

 

[Myrna Larson]

You only need 2 columns in your lookup table: in the first column you put
first date to which the rate applies. You don't need the "to October 15th
2004" part -- that is implied by the fact that in the next row you have the
date October 16, 2004. The percentage for each month goes in column C. With
this layout, the formula for A3 is

=VLOOKUP(A2,Sheet3!$B$2:$C$13,2)

If you REALLY want to see both the start date and end date, put the start date
in B2 (i.e. Sep 15, 2004), the end date in C2 (Oct 15, 2004), and the
percentage in D2. With this layout, you have to change the VLOOKUP formula to

=VLOOKUP(A2,Sheet3!$B$2:$D$13,3)


BTW, if your dates always start on the 16th of the month, you can type the
first date in B2 manually. In B3, put the formula

=DATE(YEAR(B2),MONTH(B2)+1,16)

and copy it down. If you set up a 3-column table, with the end date in column
C, the formula to put in C2 is

=B3-1