2 Que's regarding dates (matching and subtracting)

[Jacob]

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

 

[Allan S. Warrior]

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!

 

[Allan S. Warrior]

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.

 

[Allan S. Warrior]

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

 

[Robert McCurdy]

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