Suggestions and possibly answers

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Hello,
I have a workbook with 20 sheets, mostly consisting of dates and costs (the workbook is to keep track of all the income/outgoings of a small business) An current bank balance amount is entered at the start of the month and checked daily. Every debit that has been paid can then be marked off the workbook, (so far, so good).
The fixed costs that are paid every month on the same date are also done manually.
The way it is done at the moment uses a drop down list with "Paid", then on another sheet is the formula, =IF(Jan!$D2="Paid",Jan!$C2,0)

Now the question,
Is it possible to automatically mark off a payment by using the system clock?
Something along the lines of, when the due date is current, the cell will display "Paid" (if this is possible, I wont have to change any other formulas)
Also, is there any set time that this should run, before opening, midday, closing time?

If the answer is VBA, could you be specific as to where to place the code, ( still learning).

Many thanks
Colin.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Maybe a formula would work:

Where A2 has =TODAY(), =IF(A2<=TODAY(),"Paid","Outstanding")

HTH,

Smitty
 
Upvote 0
Sorry its a bit late, (just got to it)

Cheer's thats sorted it,
Colin.
 
Upvote 0
Another problem has arisen from the above post,
If A2 hasn't had a date entered (Blank Cell) the formula returns paid. there is also conditional formating on A2 =IF($A3="Paid",TRUE,0), the format turns the cell green indicating that it has been paid.
Is there something that can be added to the formula so that if there is no date in A2, the conditonal formating wont activate and A3 wont display "paid" ?
Is this clear?
Colin.
 
Upvote 0
=IF(AND(A2>0,A2<=TODAY()),"Paid","Outstanding")

Without the A2>0, Excel will treat a blank cell as having a value less then today's date. (which is a positive number).

This should do it.

Because your coditional format depends on "Paid"/"Outstanding" there should be no need to change it.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top