calculating payment terms "End of Month +30".
 

Hi All,

I am trying to have a formula that will calculate the due date on invoices that have payment terms "End of Month +30".

e.g. Invoice date May 7th, then the formule shoudl calculate 30 days from the end of May.

The invoice date is in the following format 07/05/2012.

I have been breaking my head over this. Please help.

Thanks.

Answer: calculating payment terms "End of Month +30".
 

Calculating Due Date for Invoices with Payment Terms "End of Month +30"

1. Convert the invoice date into the last day of the month using the EOMONTH function in Excel:
   
   
   Formula:

   ---

   =EOMONTH(A1,0) 


   ---

   Where A1 is the cell containing the invoice date.
   
2. Add 30 days to the last day of the month:
   
   
   Formula:

   ---

   =EOMONTH(A1,0)+30 


   ---

   Where A1 is the cell containing the invoice date.
   
3. Check if the result falls on a weekend or a holiday and adjust the due date accordingly using the WORKDAY function in Excel:
   
   
   Formula:

   ---

   =WORKDAY(EOMONTH(A1,0)+30,1) 


   ---

   Where A1 is the cell containing the invoice date.
   

Hi,

Assuming you're using at least Excel 2007 and your date is in cell A2 you can use =EOMONTH(A2,0)+30

Obviously, change the A2 for whichever cell your date is really in.

Hope that helps.

S.

Hi Spencer,

Thank you very much!
you solved my problem