Need help with adding value after specific date!

Hi All!

I recently paid for a £1,349 pc and I'm paying 41.25 on the 17th of every month.

How can I make a formula that will subtract 41.25 from 1349 after the 17th of each month So I can track how much I've got left to pay off. My current formula updates it based on month regardless of whether it's past the 17th or not.

I think I'm on the right lines with the following formula:


=SUM(1349-((((YEAR(B24)-YEAR(B23))*12+MONTH(B24)-MONTH(B23))+1)*41.25))


B24: represents the current date (updated manually, would be cool if it could automatically update but I don't know how.)

B23: represents the start of payment 17/07/2014

Thanks in advance!

Cheers man! Just used it now and it works perfectly!

Had to make a minor tweek because it didn't accommodate for the first payment on the start date. But the actual formula works like a charm!

TYVM

Originally Posted by stevenpadget

B24: represents the current date (updated manually, would be cool if it could automatically update but I don't know how.)

B23: represents the start of payment 17/07/2014

Thanks in advance!

Hi

You can use in B24 =today() formula so you will not have to add current date any more.

Just a slight improvement to the above formula:

=1349-41.25-(DATEDIF(41837,TODAY(),"m")+IF(DAY(TODAY())<17,-1,))*41.25

Now it doesn't require any other "helper" cells which specify a start or end date. The start date is hard-coded in the formula as 41837, and it is updated automatically with the TODAY() function.