2 Que's regarding dates (matching and subtracting)

From: jacob farino (jfarino_at_mindspring.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 03:28:25 GMT

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 (jfarino@amerisave.com)
      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!
"Jacob" wrote:

> 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

"Allan S. Warrior" wrote:

> 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!
> "Jacob" wrote:
>
> > 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 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.

"Jacob" wrote:

> 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 (robertwm@orcon.net.nz)
      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

"Jacob" <jfarino@amerisave.com> wrote in message
news:unsd#8VrEHA.2024@TK2MSFTNGP10.phx.gbl...
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 
begin 666 cleardot.gif
K1TE&.#EA`0`!`(#_`,# P ```"'Y! $`````+ `````!``$```("1 $`.P``
`
end
Quantcast