Need date formula to display answer in a particular cell

[Phil Buchman]

Hi Everyone,

This is probably very easy for Excel gurus, but my expertise is in
writing websiters and not so much in Excel---so I need a little help
with this one.

In English, I need a date formula to display answer in a particular
cell:

To be written in cell G39
"If today is between March 10 and May 9, then put the answer to
=((H40-N11)*N6)+E26 in this cell"

To be written in cell G40
"If today is between May 10 and July 9, then put the answer to
=((H40-N11)*N6)+E26 in this cell"

To be written in cell G41
"If today is between July 10 and September 9, then put the answer to
=((H40-N11)*N6)+E26 in this cell"

To be written in cell G42
"If today is between September 10 and November 9, then put the answer
to =((H40-N11)*N6)+E26 in this cell"


Thanks for your help!

 

[Phil Buchman]

Oops, I made a mistake already!

Let me amend that original request to include the following:

"If today is between March 10 and May 9 of this year, regardless of
which year it is, then put the answer to
=((H40-N11)*N6)+E26 in the cell where this formula is written. But if
today is not within that date range, then don't modify the last answer
that was placed in this cell by this formula during that period of
time"


Like I said, I'm not an Excel guru so I have trouble articulating
exactly what I need. Sorry for the confusion. I'll be monitoring this
newsgroup to see if anyone has any questions for me concerning
this---I'll try to help as best I can but I'm kind of a low-IQ guy with
Excel

Again, thanks for your help and understanding!

 

[Sandy Mann]

"If today is between March 10 and May 9 of this year, regardless of which

year it is, then put the answer to
=((H40-N11)*N6)+E26 in the cell where this formula is written. But if
today is not within that date range, then don't modify the last answer
that was placed in this cell by this formula during that period of time"

Why should the answer be different because the date is different?

In other words what is changing to make the answer different?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernard Liengme]

Very hard to satisfy the last requirement - do not change current value if
TODAY is outside your range. We do not know how the current value is
obtained.

Anyway, here is something to start with
=IF(AND(TODAY()>=DATE(YEAR(TODAY()),3,10),TODAY()<=DATE(YEAR(TODAY()),5,9)),"OK","Not
OK")

IF the current day falls within the range March 10 to May 9 (inclusive) then
the formula returns OK, otherwise it returns "Not OK"
You could replace "OK" by your formula ((H40-N11)*N6)+E26 giving:

=IF(AND(TODAY()>=DATE(YEAR(TODAY()),3,10),TODAY()<=DATE(YEAR(TODAY()),5,9)),
((H40-N11)*N6)+E26 ,"Not OK")

As Sandy says, we need to know more.
best wishes

 

[Phil Buchman]

Why should the answer be different because the date is different?

In other words what is changing to make the answer different?

I have a two-month time limit to use an allotment of irrigation water.
The formula =((H40-N11)*N6)+E26 tracks how much manual irrigation that
I have used during that two month period.

At the end of the March 10-May 10 period, I begin a new two month
period with another allotment of water for May 10-July 10. I want to
keep track of how much manual-irrigation water was used during March
10-May 10 period in one cell of the spreadsheet, and also keep track of
how much manual-irrigation water is being used during the May 10-July
10 period in another cell.

Here is the problem: cells H40, N11, N6 and E26 contain numbers that
change at the beginning of each two month period. If the cell that
contains the March 10-May 10 period total is connected to H40, N11, N6
and E26 by an functioning formula, then the numbers will change
accordingly. I don't want the numbers in the March 10-May 10 cell to
change once the date moves beyond May 10th.

Am I explaining this correctly?

 

[Sandy Mann]

Formulas are volatile in the sense that that always calculate using the
current value in cells - they have no memory.

To keep the value in a call you either have to copy it and paste the value
back using Paste Special > Values or use VBA to overwrite the formula with a
value automatically on opening the Workbook..

Post back if you need any further help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Phil Buchman]

Hi Sandy,

Formulas are volatile in the sense that that always calculate using the
current value in cells - they have no memory.

To keep the value in a call you either have to copy it and paste the value
back using Paste Special > Values or use VBA to overwrite the formula with a
value automatically on opening the Workbook..

Post back if you need any further help.

Thanks! Knowing that my idea won't work in Excel is helpful because it
allows me to explore other potential solutions without constantly
wondering if Excel would've done the job with a formula that I didn't
know existed.

 

[Roger Govier]

Hi Phil

I think you can do this quite easily in Excel, if there is a log of your
actual irrigation.

If you have 2 columns, one with date and the other with mm of water
applied, then a simple set of Sumproduct formulae would calculate the
total usage between any sets of dates you require.
These values could then be subtracted from your quota for the same
periods, to show what balance you have still available to use.