| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
due dates
Is there a formula that can calculate future monthly and semi monthly due
dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
Take a look at Excel Help for the EOMONTH() worksheet function. It is part
of the Analysis ToolPak add-in in pre-2007 versions of Excel, so you may need to install that before the function is available to you. "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
will this also help with the 2nd part of my question?
"JLatham" wrote: Take a look at Excel Help for the EOMONTH() worksheet function. It is part of the Analysis ToolPak add-in in pre-2007 versions of Excel, so you may need to install that before the function is available to you. "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
For the 2nd part you will need an If statement. Something like:
=if(day(a1)=16,eomonth(a1,0),date(year(a1),month(a 1)+1,16)) Regards, Fred. "GAIDEN" wrote in message ... will this also help with the 2nd part of my question? "JLatham" wrote: Take a look at Excel Help for the EOMONTH() worksheet function. It is part of the Analysis ToolPak add-in in pre-2007 versions of Excel, so you may need to install that before the function is available to you. "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
Hi,
Enter a start date like 1/16/09 in A1, then in A2 and A3 enter these two formulas =EOMONTH(A1,0) =EDATE(A1,1) Highlight A2:A3 and fill down as far as needed -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
Hi,
We failed to mention that these two functions are part of the Analysis ToolPak in 2003 and earlier although they are part of Excel in 2007. You must attach the above add-in by choosing Tools, Add-ins, and checking Analysis ToolPak. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
Is there a formula that can calculate future monthly and semi monthly due
dates correctly. If you are looking to find the last day of a month try =DATE(YEAR(A1),MONTH(A1)+1,1)-1 To find the next semimonth due date =DATE(YEAR(A1),IF(DAY(A1)<16,MONTH(A1),MONTH(A1)+1 ),16) Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 Try DATEDIF() A1 = 05-31-2009 B1 = DATEDIF(A1,C1,"d") C1 = 06-30-2009 D1 = DATEDIF(C1,E1,"d") E1 = 07-31-2009 If this post helps click Yes --------------- Jacob Skaria "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
due dates
Actually, I thought about that part later, and if you take the end of the
PREVIOUS month and add 15 to it, you will always get the 15th of the month in question. Which may be what Fred's formula is actually doing. i.e. Feb 28 or 29 + 15 days = March 15; Oct 31 + 15 days = Nov 15th, etc... "Fred Smith" wrote: For the 2nd part you will need an If statement. Something like: =if(day(a1)=16,eomonth(a1,0),date(year(a1),month(a 1)+1,16)) Regards, Fred. "GAIDEN" wrote in message ... will this also help with the 2nd part of my question? "JLatham" wrote: Take a look at Excel Help for the EOMONTH() worksheet function. It is part of the Analysis ToolPak add-in in pre-2007 versions of Excel, so you may need to install that before the function is available to you. "GAIDEN" wrote: Is there a formula that can calculate future monthly and semi monthly due dates correctly. Example: 05-31-2009 + ???? = 06-30-2009 + ???? = 07-31-2009 Example: 05-16-2009 + ???? = 05-31-2009 + ???? = 06-16-2009 + ???? = 06-30-2009 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop dates from showing as numbers - when formated as dates | Excel Discussion (Misc queries) | |||
| compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
| how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Linear Mode
