# Thread: Formula problem (Excel 2003)

1. ## Formula problem (Excel 2003)

Hello everybody

I am trying to calculate outstanding maintenace fees on a spreadsheet based on a report date.
I attach a sample spreadsheet with limited info to illustrate.
The formula should calculate fees due by adding fees as per the Monthly Fees row from the beginning of the spreadsheet, in this case column D upto and including the reporting month date as typed in. That mean if the report month is changed the answer will change automatically.

In the same way the fees paid upto the reporting month is calculated and the fees payable deducted to get the positive or negative balance.

Any help will be appreciated

2. ## Re: Formula problem (Excel 2003)

See attached version. I changed the way the year and month are concatenated in order to be able to compare dates.
Your sample wasn't entirely correct since the monthly fee changes.

3. ## Re: Formula problem (Excel 2003)

Hans

Thank you very much for the solution.

And you are correct my sample was wrong, BUT YOUR FORMULA WORKS CORRECT

As always I appreciate your help very much, just for educational purposes, what is the difference between the two date concatonations?

Regards

4. ## Re: Formula problem (Excel 2003)

You coded the month as myyyy. For example, May 2006 becomes 52006. This is sufficient to compare equality of two months, but not to know whether one month is earlier or later than another one, at least not without additional calculations, take (for example) 92006, 122006 and 12007 cannot easily be sorted in the correct order, whether you see them as numbers or as text.
I used yyyymm; for May 2006 this becomes 200605. This makes it easy to compare months: 200609 < 200612 < 200701.

5. ## Re: Formula problem (Excel 2003)

Hans

Thank you for the explanation, I will remember it for the future.

Regards

6. ## Re: Formula problem (Excel 2003)

Hans

May I tap your brain a bit more on this one?

I attach the same workbook as before with a little information in.

What I am trying to do is to calculate the previous total also, so if my report date is Feb-07 then the formula must automatically based the calculation on the previous month in this case Jan-07 and so on. I was thinking of adding another reference cell for the calculation but do not know how to get the new cell to reduce automatically by one month.

Regards

7. ## Re: Formula problem (Excel 2003)

Use this formula in K7:

=TEXT(DATE(YEAR(C3),MONTH(C3)-1,1),"yyyymm")

The expression DATE(YEAR(C3),MONTH(C3)-1,1) returns the first day of the previous month to that in C3. You can then use

=SUMPRODUCT((\$D\$7:\$H\$7<=\$K\$7)*(-\$D\$4:\$H\$4+\$D8:\$H8))

in K8 and fill down.

8. ## Re: Formula problem (Excel 2003)

Hans

Thank you very much for the quick respons, I realy appreaciate it.

Enjoy the weekend

9. ## Re: Formula problem (Excel 2003)

Hans

I was wondering, after I used the formula as stated earlier and I get an answer of 200708 which is in the format of =TEXT(AF6,"yyyymm") how can I get the display of 200708 back to displaty Aug-2007

Regards

10. ## Re: Formula problem (Excel 2003)

Why would you want that? The "real" dates are already available in other cells, so there should be no need to convert the text string back to a date.

11. ## Re: Formula problem (Excel 2003)

Hans

I am using the formulas in more than one report, so in short what I want to do in the following for example:

If I calculate how much money was paid by a certain report date I then take the overpayment/underpayment and devide that by the monthly fee to see how many months the person is paid in advance or behind.
I then add that to the report date to get for instance 200708, now for the people reading the reports it will be easier to read the result on the format of Aug-07

12. ## Re: Formula problem (Excel 2003)

You should enter the report date as a date, e.g. 01-Aug-2007, and format the cell as mmm-yy. Use this in your report.
Use a formula such as =TEXT(A4,"yyyymm") to obtain the text string 200708, and use this for the calculations.
You can hide the row or column with this text value if desired.

13. ## Re: Formula problem (Excel 2003)

Hans

Thank you, problem is solved

Regards

14. ## Re: Formula problem (Excel 2003)

Good morning

My spreadsheet works almost perfect now save one more modification.

I attach a small extraction for easy reference and explanation.

Currently the formula in Column O refers to Column E as the starting date for all
I know I can edit each row's formula to take different stating dates as in Column C into account, but
How can the formulas in Column O be adjusted as to take different start dates as in Column C into account automatically?

Regards

15. ## Re: Formula problem (Excel 2003)

You already calculated the formatted text values of the start dates in column D, so you should be able to use

=SUMPRODUCT((\$E\$7:\$M\$7<=\$D8)*(\$E8:\$M8-\$E\$4:\$M\$4))

in O8 and fill down. \$C\$7 has been replaced with \$D8. Note that the row number is relative now because you want to use different starting dates depending on the row.

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•