spreadsheet help

C

colsey25

Joined
Jun 23, 2011
Messages
4
Reaction score
0
hi i have a spreadsheet that tells me outstanding fees for a local club i run, i woul like to have it tell me when the amount is 7 days outstanding, 14 days outstanding and then 30 days outstanding, i have the due amonunt in cell g3.

is this possible? thanks for any help in advance
 
Yes it should be doable, however how do you know the due date; i see you gave the cell for due amount, but if you want to base your formatting on due time, I guess you must haver a due amount somewhere in the spreadsheet ?

Sifou
 
the due date will be 14 days from invoice, so the amount needs to move to the relevant columbs as it gets past 7 days 14 days etc

cheers for your help
 
Hi I am a bit confused. could you send me the spreadsheet or a dummy one similar to yours and if you can also explain me which cells should move where based on which other cells. Thanks

Sorry for the hassle but your issue seems quite specific and I am struggling modeling it on my test spreadsheet :-)

Sifou
(e-mail address removed)
 
it removed your email addy. i would just like an amount say £10.00, putting in a columb once 7 days expire, then in a columb when 14 days expire etc.
 
I see your issue, but how does the macro get the date of when the 10£ where due, where is the date held, in which cell ? so that the macro can compare it to the date the macro is run or every time you open the file if it's how you want to proceed ?

the email is removed on the site so that other users submit posts rather than contacting the good subscribers directly, which would reduce the information shared. the email should not be hidden in the email alerting you that your post has been updated by someone, so have a look, you may have received an email where my email address is not hidden. Except if there is a way to deactivate the alerts and that you have done so :-)
 
the invoice date will be held in cell a3.so need it to put the value amount in columb after the dates outstanding
 
Colsey25,

Have you thought about adding a column to show the difference between the due date and =now(), then using a pivot table to provide you the information?
The =now() cell could be referenced absolutely (f4 adds $c$r) to the formula, you would need to refresh the data to show what is current. In the pivot table you could add the difference column to the report and group by 7,14,21 etc.

Stoneboysteve
 
This looks like it needs an IF statement.

Cell B2 contains =TODAY()
Cell B5 contains the invoice date
Cell D4 contains =IF(B2-B5>21,40,IF(B2-B5>14,20,IF(B2-B5>7,10,0)))

B2 will show today's date when the sheet opens. B5 is input by the user. D4 will return the value £40.00 if the invoice is more than 21 days outstanding, £20.00 if more than 14 days outstanding and £10.00 if more than 7 days outstanding. If less than 7 days outstanding it will return £0.00
 
Back
Top