month calculations
 

I am trying to set up a spredsheet to calculate my finances and i have some
direct debits that come up quartely and some bi monthly. I want the
spreadsheet to work out when.

So if one comes up today 31/3/07 and is due up in 3 months how can i get it
to work out when i.e. 30/6/07. I dont want to have to sit with a calander and
work out how many days it is and add them t the current cell.

Also leading on from that how could i tell it i get paid on the last day of
the month and have it work it out. i.e. 28/2, 31/3 etc

month calculations
 

See EOMONTH function:

If first date is in A1:

=EOMONTH(A1,3) will date 3 months hence

=Date (year,month+1,0) where "month" is current month will give last day of
"month"

=Date(2007,2,0) will date of 31st Jan 2007
=Date(2007,3,0) will date of 28th Feb 2007



HTH


"drizzy" wrote:

I am trying to set up a spredsheet to calculate my finances and i have some
direct debits that come up quartely and some bi monthly. I want the
spreadsheet to work out when.

So if one comes up today 31/3/07 and is due up in 3 months how can i get it
to work out when i.e. 30/6/07. I dont want to have to sit with a calander and
work out how many days it is and add them t the current cell.

Also leading on from that how could i tell it i get paid on the last day of
the month and have it work it out. i.e. 28/2, 31/3 etc

month calculations
 

A1 has 31/3/07
A2 has =DATE(YEAR(A1).MONTH(A1)+3,DAY(A1))
A2 will have the date three months from A1's date

B1 has month number (say 2 for Feb)
B2 has formula =DATE(YEAR(TODAY),month(B2)+1,0)
B2 displays last day of month represented by B1

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"drizzy" wrote in message
...
I am trying to set up a spredsheet to calculate my finances and i have some
direct debits that come up quartely and some bi monthly. I want the
spreadsheet to work out when.

So if one comes up today 31/3/07 and is due up in 3 months how can i get
it
to work out when i.e. 30/6/07. I dont want to have to sit with a calander
and
work out how many days it is and add them t the current cell.

Also leading on from that how could i tell it i get paid on the last day
of
the month and have it work it out. i.e. 28/2, 31/3 etc

month calculations
 

Please note that if you are using the EOMONTH function, you have to install
the AnalysisToolPak utility.

----
Thanks !
S

"Bernard Liengme" wrote:

A1 has 31/3/07
A2 has =DATE(YEAR(A1).MONTH(A1)+3,DAY(A1))
A2 will have the date three months from A1's date

B1 has month number (say 2 for Feb)
B2 has formula =DATE(YEAR(TODAY),month(B2)+1,0)
B2 displays last day of month represented by B1

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"drizzy" wrote in message
...
I am trying to set up a spredsheet to calculate my finances and i have some
direct debits that come up quartely and some bi monthly. I want the
spreadsheet to work out when.

So if one comes up today 31/3/07 and is due up in 3 months how can i get
it
to work out when i.e. 30/6/07. I dont want to have to sit with a calander
and
work out how many days it is and add them t the current cell.

Also leading on from that how could i tell it i get paid on the last day
of
the month and have it work it out. i.e. 28/2, 31/3 etc