Edward
Sat Mar 15 10:19:16 CDT 2008
This is a multi-part message in MIME format.
------=_NextPart_000_0020_01C886AF.EFB6EA00
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
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 =C2=A3900 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.=20
Ed
"Shane Devenshire" <shanedevenshire@sbcglobal.net> wrote in message =
news:uXSGqwqhIHA.4712@TK2MSFTNGP04.phx.gbl...
Hi Ed,
Are you interested in this for the first of each month or just the =
month of March? =20
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=20
"Edward" <edward.j.obrien@DELETEbtinternet.com> wrote in message =
news:2B607DE0-A52B-478F-A29B-D5F909B41922@microsoft.com...
> Thanks, Max. I will be keeping this formula anyway, but the figure =
of =C2=A3900=20
> is merely typed in, representing a budget outlay, and is not the =
result of=20
> any formulae. Can you help me with that?
>=20
> Ed
>=20
>=20
> "Max" <demechanik@yahoo.com> wrote in message=20
> news:75E4CE7B-39FD-4884-BABE-756E0E300A32@microsoft.com...
>> 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: =3DSUM(A4:C4)
>> You could refashion it like this for D4:
>> =3DIF(TODAY()=3D --"01-Mar-2009",0,SUM(A4:C4))
>> --=20
>> Max
>> Singapore
>>
http://savefile.com/projects/236895
>> xdemechanik
>> ---=20
>
------=_NextPart_000_0020_01C886AF.EFB6EA00
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY id=3DMailContainerBody=20
style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"=20
bgColor=3D#ffffff leftMargin=3D0 topMargin=3D0 name=3D"Compose message =
area"=20
CanvasTabStop=3D"true">
<DIV><FONT face=3DArial size=3D2>Hi, Shane.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I run an annual (12 months committed) =
expenditure=20
programme for various facilities which are paid monthly. However, the=20
payments are not all divided by 12 months. Some run only =
for 10=20
months, while others may run for 9 months or 8 months. =
Each=20
monthly deficit is shown alongside the main figure. (I used =C2=A3900 in =
the=20
example). These will show zero as the main figure reverts =
to=20
zero.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>When a last payment is made I want the =
cell to=20
revert to zero and remain at zero until the start of the new =
financial=20
year when I change the formulae (move it up a year) to accommodate =
the new=20
programme. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Ed</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Shane Devenshire" <<A=20
=
href=3D"mailto:shanedevenshire@sbcglobal.net">shanedevenshire@sbcglobal.n=
et</A>>=20
wrote in message <A=20
=
href=3D"news:uXSGqwqhIHA.4712@TK2MSFTNGP04.phx.gbl">news:uXSGqwqhIHA.4712=
@TK2MSFTNGP04.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>Hi Ed,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Are you interested in this for the =
first of each=20
month or just the month of March? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>There are at least two possibilities =
1. you can=20
format the cell with conditional formatting to Hide, but not remove =
the value=20
in the cell on the first of March or the first of any month, 2. You =
can write=20
a macro that clears the cell on the first of march or the first of =
each=20
month.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>With the conditional formatting =
applied, the=20
number will reappear on the 2nd of the month - it is not clear from =
your=20
question if that is acceptable.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>With either approach a complicating =
factor is how=20
do you want to handle the situation if the first falls on a Saturday =
or=20
Sunday, so the file is opened for the first time on the 2nd or =
3rd? =20
Again your question does not make this clear.</FONT></DIV><FONT =
face=3DArial=20
size=3D2></FONT><FONT face=3DArial size=3D2></FONT>
<DIV><BR><FONT face=3DArial size=3D2>Cheers,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Shane </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><BR>"Edward" <edward.j.obrien@DELETEbtinternet.com> wrote =
in=20
message =
news:2B607DE0-A52B-478F-A29B-D5F909B41922@microsoft.com...<BR>>=20
Thanks, Max. I will be keeping this formula anyway, but the figure of =
=C2=A3900=20
<BR>> is merely typed in, representing a budget outlay, and is not =
the=20
result of <BR>> any formulae. Can you help me with that?<BR>> =
<BR>>=20
Ed<BR>> <BR>> <BR>> "Max" <demechanik@yahoo.com> wrote =
in=20
message <BR>>=20
news:75E4CE7B-39FD-4884-BABE-756E0E300A32@microsoft.com...<BR>>> =
On the=20
face of this:<BR>>>> .. I have the sum of 900 in cell D4 .. =
which I=20
want<BR>>>> to read zero on the 1st March=20
2009.<BR>>><BR>>> Suppose the formula in D4 is currently:=20
=3DSUM(A4:C4)<BR>>> You could refashion it like this for =
D4:<BR>>>=20
=3DIF(TODAY()=3D --"01-Mar-2009",0,SUM(A4:C4))<BR>>> -- =
<BR>>>=20
Max<BR>>> Singapore<BR>>>=20
http://savefile.com/projects/236895<BR>>> =
xdemechanik<BR>>> ---=20
<BR>></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0020_01C886AF.EFB6EA00--