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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Maybe a formula would work:

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

HTH,

Smitty
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Sorry its a bit late, (just got to it)

Cheer's thats sorted it,
Colin.
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,269
Messages
5,600,629
Members
414,396
Latest member
rinianjell

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
Top