Conditional Formatting - Date Initiated

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

I have the sum of £900 in cell D4 (and other cells with sums) which I want
to read zero on the 1st March 2009.

Is there a formula I can use to return a cell to zero on a specific date?

TIA

Ed
 
.. I have the sum of 900 in cell D4 .. which I want
to read zero on the 1st March 2009.
Click to expand...

Suppose the formula in D4 is currently: =SUM(A4:C4)
You could refashion it like this for D4:
=IF(TODAY()= --"01-Mar-2009",0,SUM(A4:C4))

---
 
Thanks, Max. I will be keeping this formula anyway, but the figure of £900
is merely typed in, representing a budget outlay, and is not the result of
any formulae. Can you help me with that?

Ed
 
Thanks again, Max.

It's good to know there are helpful experts out there.

One more thing - It may be a good idea for me to test it. Hate to set all
the entries up only to find I've done something wrong. Does it run off the
Windows clock or the motherboard?

Ed
 
Hi Ed,

Are you interested in this for the first of each month or just the month of March?

There are at least two possibilities 1. you can format the cell with conditional formatting to Hide, but not remove the value in the cell on the first of March or the first of any month, 2. You can write a macro that clears the cell on the first of march or the first of each month.

With the conditional formatting applied, the number will reappear on the 2nd of the month - it is not clear from your question if that is acceptable.

With either approach a complicating factor is how do you want to handle the situation if the first falls on a Saturday or Sunday, so the file is opened for the first time on the 2nd or 3rd? Again your question does not make this clear.

Cheers,
Shane
 
Hi, Shane.

I run an annual (12 months committed) expenditure programme for various facilities which are paid monthly. However, the payments are not all divided by 12 months. Some run only for 10 months, while others may run for 9 months or 8 months. Each monthly deficit is shown alongside the main figure. (I used £900 in the example). These will show zero as the main figure reverts to zero.

When a last payment is made I want the cell to revert to zero and remain at zero until the start of the new financial year when I change the formulae (move it up a year) to accommodate the new programme.

Ed

Hi Ed,

Are you interested in this for the first of each month or just the month of March?

There are at least two possibilities 1. you can format the cell with conditional formatting to Hide, but not remove the value in the cell on the first of March or the first of any month, 2. You can write a macro that clears the cell on the first of march or the first of each month.

With the conditional formatting applied, the number will reappear on the 2nd of the month - it is not clear from your question if that is acceptable.

With either approach a complicating factor is how do you want to handle the situation if the first falls on a Saturday or Sunday, so the file is opened for the first time on the 2nd or 3rd? Again your question does not make this clear.

Cheers,
Shane
 
Back
Top