due dates

  • Thread starter Thread starter GAIDEN
  • Start date Start date
G

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
 
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.
 
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.
 
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
 
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.
 
Is there a formula that can calculate future monthly and semi monthly due
dates correctly.
Click to expand...

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
Click to expand...

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