due dates

[GAIDEN]

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

 

[JLatham]

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]

will this also help with the 2nd part of my question?

 

[Fred Smith]

For the 2nd part you will need an If statement. Something like:
=if(day(a1)=16,eomonth(a1,0),date(year(a1),month(a1)+1,16))

Regards,
Fred.

 

[Shane Devenshire]

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

 

[Shane Devenshire]

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.

 

[Jacob Skaria]

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

 

[JLatham]

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...