date function

[boardmaker]

Sometimes I feel stupid. I've been trying to create a formula for hours
and never thought to look for a forum. Is there a way that if I enter a
date in say A1 as 5/4/06 that excel will then in A2 subtract that date
from the end of it's month to give me the remaining days. But the
formula needs to work for all months. So if I enter that date it will
go to the month of may and subtract. But, my next date may be 2/4/04.
So it need to work with all months. Is this even possible? Thanks for
the help and I know I will have a lot more questions for everyone.

 

[Guest]

Hi
Try this:
=DATE(YEAR(A2),MONTH(A2)+1,0)-A2
with your date in A2
Format the cell as general.

AndyB.

 

[boardmaker]

I just tried that with 2/2/2004 and I got 1/27/1900. Any idea why

 

[Guest]

format result as general NOT dates


Regards


Peo Sjoblom

 

[Guest]

Hy,

You can use function DATADIF to calculate the difference between two dates,
now, for the formula month functions for every month would be interesting to
have a table with the number of each and the last day of it, as eg.
A B
1 01/31/2006
2 02/28/2006
....
12 12/31/2006

Then the formula could be:
assuming that you have the date on the A column and the table above on range
C617

=DATEDIF(A1,VLOOKUP(MONTH(A1),$C$6:$D17,2,0),"D")

Hope it helps
Regards from Brazil
Marcelo








"boardmaker" escreveu:

 

[Guest]

Hy,

You can use function DATADIF to calculate the difference between two dates,
now, for the formula month functions for every month would be interesting to
have a table with the number of each and the last day of it, as eg.
A B
1 01/31/2006
2 02/28/2006
....
12 12/31/2006

Then the formula could be:
assuming that you have the date on the A column and the table above on range
C617

=DATEDIF(A1,VLOOKUP(MONTH(A1),$C$6:$D17,2,0),"D")

Hope it helps
Regards from Brazil
Marcelo


"Marcelo" escreveu: