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

On the face of this:
> .. I have the sum of 900 in cell D4 .. which I want
> to read zero on the 1st March 2009.

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"Max" <(E-Mail Removed)> wrote in message
news:75E4CE7B-39FD-4884-BABE-(E-Mail Removed)...
> On the face of this:
>> .. I have the sum of 900 in cell D4 .. which I want
>> to read zero on the 1st March 2009.
>
> 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))
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

> .. but the figure of £900 is merely typed in, ..
> and is not the result of any formulae.

Perhaps this, then? :
In D4: =IF(TODAY()= --"01-Mar-2009",0,900)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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



"Max" <(E-Mail Removed)> wrote in message
news:36B06BD8-E749-42B7-876A-(E-Mail Removed)...
>> .. but the figure of £900 is merely typed in, ..
>> and is not the result of any formulae.
>
> Perhaps this, then? :
> In D4: =IF(TODAY()= --"01-Mar-2009",0,900)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

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



"Edward" <(E-Mail Removed)> wrote in message news:2B607DE0-A52B-478F-A29B-(E-Mail Removed)...
> 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
>
>
> "Max" <(E-Mail Removed)> wrote in message
> news:75E4CE7B-39FD-4884-BABE-(E-Mail Removed)...
>> On the face of this:
>>> .. I have the sum of 900 in cell D4 .. which I want
>>> to read zero on the 1st March 2009.
>>
>> 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))
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>

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

"Shane Devenshire" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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



"Edward" <(E-Mail Removed)> wrote in message news:2B607DE0-A52B-478F-A29B-(E-Mail Removed)...
> 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
>
>
> "Max" <(E-Mail Removed)> wrote in message
> news:75E4CE7B-39FD-4884-BABE-(E-Mail Removed)...
>> On the face of this:
>>> .. I have the sum of 900 in cell D4 .. which I want
>>> to read zero on the 1st March 2009.
>>
>> 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))
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>