Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Month Timetable on a worksheet different month each works | Excel Worksheet Functions | |||
End of month calculations | Excel Discussion (Misc queries) | |||
Month to date calculations | Excel Worksheet Functions | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |