Need the formula to calculate the pay terms for a project.
Hi,
I am working on a new project in my current organisation. I need formula to calculate the exact payment date. Below is the details explanation.
Payment term Batch period
AP45 16th of the month-15th of the next month
AP55 16th of the month-15th of the next month
AP60 1st of the month-Last date of the same month
AP75 24th of a month-23rd of the next month
AP45 means 45 days & AP55 means 55 days so on for remaining two. So if customer sends something i.e invoice to us, then it got received for example today & it falls under AP45 category, then the 45 days should be calculated from 16 of next month according to this category AP45. And if it is received BEFORE 16th(not on 16th) in a month then calculation of these 45 days should be from 16th of the same month.
I need the formula to do this calculation. Need your help guys.
Thanks in advance.
Regards,
Ravi.
ravik453@gmail.com
I'll start you off.. last day
I'll start you off..
last day of the current month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
First day of the current month
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
16th day of the current month
=DATE(YEAR(TODAY()),MONTH(TODAY()),16)
nth Day of next month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,n)
- replace n with the number you want
read more:
http://excelexperts.com/Excel-Tips-Calculate-the-LAST-Day-of-the-month
Need the formula to calculate the pay terms for a project.
Thank you so much Nick. That solved the problem.
Regards,
Ravi.