Date Dependent Formula

  • Thread starter Thread starter tanya
  • Start date Start date
T

tanya

I have created a spreadsheet to caluculate child support
due from my ex. Each late payment accrues 9% per annum
interest. I would like to know if there is some way to
write a formula that takes the system date into account
and automatically figures out how many weeks late a
payment is. Right now I have a column with the formula
((B_*9%)/52)*"number of weeks late" where B_ is column B
any row and is the amount of the child support payment and
the "number of weeks late" is manually put in by me. It
is getting very cumbersome to keep updating the number of
weeks late. Any suggestions would be awesome!

Thanks
 
Tanya
You could substitute this for "number of weeks late"

WEEKNUM(TODAY())-WEEKNUM(due_date

You will need to have the due date somewhere in your information

For some more good information on date difference see Chip Pearson's site
http://www.cpearson.com/excel/datedif.ht

Good Luck
Mark Graesse
(e-mail address removed)

----- tanya wrote: ----

I have created a spreadsheet to caluculate child support
due from my ex. Each late payment accrues 9% per annum
interest. I would like to know if there is some way to
write a formula that takes the system date into account
and automatically figures out how many weeks late a
payment is. Right now I have a column with the formula
((B_*9%)/52)*"number of weeks late" where B_ is column B
any row and is the amount of the child support payment and
the "number of weeks late" is manually put in by me. It
is getting very cumbersome to keep updating the number of
weeks late. Any suggestions would be awesome

Thank
 
I have the due date in column A so the formula would look
like this??
((B3*9%)/52)*(WEEKNUM(TODAY())-WEEKNUM(A3))
 
Tanya,
You got the formula right, but I noticed that it won't work if the due date is in the previous year. I redid the formula to take the number of days between the due date and today, divide it by 7, and round to the nearest whole number.

=((B3*9%)/52)*ROUND(DATEDIF(A3,TODAY(),"d")/7,0)

This will work regardless of which years the dates are in.

As a side note, your interest formula does not compound the interest. Interest is typically compounded monthly, and may increase the amounts you are owed.

Good Luck,
Mark Graesser
(e-mail address removed)

----- (e-mail address removed) wrote: -----

I have the due date in column A so the formula would look
like this??
((B3*9%)/52)*(WEEKNUM(TODAY())-WEEKNUM(A3))
 
Back
Top