Formula Help- Date and Currency

[dougw]

I want to create a formula that determines how much
money is left for a given date. My column B is the
amount of money given annually. My column D is
the date it was given. I would like colum E to reflect
how much money is left to date. Is this possible?
In a sense, if I would open the file another day in
the future and the number in colum E reflect the
current amount based on the current date

 

[Guest]

Dougw,

Assuming that the amount given annually is in B2 and the date given is in
D2, then the formula:

=CONCATENATE(TEXT(B2-((TODAY()-D2)*(B2/365)),"$#,##0.00;[Red]-$#,##0c"),"
left at ",TEXT(TODAY(),"DD/MM/YYYY")) will give the following result:

Col A Col B Col C Col D Col E
$8000.00 27/05/2004 $5,435.62 left at
21/09/2004

This assumes that the money given is equally divided throughout the year so
that at the end of the year nothing is left. That's what the B2/365 bit of
the formula does.

If you have a set amount per day during the year enter that amount in
another column (e.g. Column F) and Replace (B2/365) with the reference for
the amount (e.g. F2)

Thus:
=CONCATENATE(TEXT(B2-((TODAY()-D2)*F2),"$#,##0.00;[Red]-$#,##0c")," left at
",TEXT(TODAY(),"DD/MM/YYYY")) will give the following result:

Col A Col B Col C Col D Col E
Col F
$8000.00 27/05/2004 -$2,530.00 left at
21/09/2004 $90.00

unlikeKansas