J
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!
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
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.
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
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!
Jacob said: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 said: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.
Jacob said: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