|
|
|
|
you're welcome ... glad we got there in the end :)
"Jo of TX" wrote in message
...
Julie D,
YOU ARE A LIFE SAVER. THANK YOU THANK YOU!!!!!
"JulieD" wrote:
Hi Jo
sorry try
=IF(OR(A2="N",A3="N"),TODAY()+140,IF(A6<"",A6+112 ,0))
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jo of TX" wrote in message
...
I don't think I explained myself correctly.
Everything with cells A2, A3 & getting a date 20 weeks out from today
in
A5
all work great. But how would I add to the formuls that if a date like
3-22-05 is in A6 then I want A5 to show a date of A6 plus 16 weeks. If
no
date is in A6 then cell A5 shows todays date plus 20 weeks.
Does this make more sense?
"JulieD" wrote:
Hi Jo
the only problem i see with what you're asking is that there is no
function
to test if something is a date as excel stores dates as numbers ...
you
could however test that the "number" in A6 is within certain
parameters
and
then "assume" that therefore it is a date - OR hopefully, someone else
might
have an idea of how to test for a date ... but going on what i know i
would
use the formula
=IF(OR(A2="N",A3="N"),TODAY()+140,IF(AND(A6=38353 ,A6<=TODAY()+140),TODAY()+112,0))
here i'm testing to see that the number in A6 is greater than or equal
to
1/1/05 and less than or equal to the current date plus 20 weeks.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jo of TX" wrote in message
...
Thank you for the help with the first part of my problem. Before I
try
the
2nd part, I had a question that might make it easier. Could
something
be
added to the A5 formula that if both A2 & A3 have a "Y" and A6 has a
date
it
in then A5 would show a date of 16 weeks from the date in A6?
I have to make t he workbook available to several of my co-workers
to
update
the A2, A3, & A6 cells and if needs to be as dummy proof as it can
be.
They
come to me with their problems. Not a good sign.
"JulieD" wrote:
Hi Jo
If A2 & A3 each have a "N" in it then I want cell A5 to show
today's
date
plus 20 weeks. However, if only 1 of the cells A2 or A3 has a
"N"
in I
still
want A5 to show today's date plus 20 weeks.
formula in A5
=IF(OR(A2="N",A3="N"),TODAY()+140,0)
(you might need to format A5 as a date)
If both A2 & A3 have "Y" in them then I want A5 to
freeze with the date last show when 1 of the two cells had a "N"
it
it.
this is harder to do, as the only way i see that you could do it is
to
keep
a record somewhere of the value stored in A5 so if both changed to
a Y
you
could use a worksheet_change event to change the formula to a
value -
this
will have to be done through code.
e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = "$A$2" Or Target.Address = "$A$3" Then
If Application.WorksheetFunction.CountIf(Range("A2:A3 "),
"Y")
= 2
Then
Range("AA5").Copy Range("A5")
Else
Range("A5").Copy
Range("AA5").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Target.Offset(1, 0).Select
End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
------
to use this code, right mouse click on the sheet tab of the sheet
containing
A2 & A3 and choose view copy, copy & paste the above code into the
right
hand side of the screen ...
switch back to your workbook and try it out
note, i'm storing the value of A5 in AA5 ... you might need to
change
this
cell reference if you have something else in AA5
note, the formula won't be re-instated into cell A5 if you change
one
or
both to a N if they have both been Y's
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jo from TX" <Jo from wrote in
message
...
I am trying to write a formula that works off what is in two
separate
cells.
It has several parts and maybe not all of them can be done. It
would
be
great if someone could help me out. Here goes.
If A2 & A3 each have a "N" in it then I want cell A5 to show
today's
date
plus 20 weeks. However, if only 1 of the cells A2 or A3 has a
"N"
in I
still
want A5 to show today's date plus 20 weeks.
Can this also be done? If both A2 & A3 have "Y" in them then I
want
A5
to
freeze with the date last show when 1 of the two cells had a "N"
it
it.
|