Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Formula
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. |
#2
|
|||
|
|||
Hi Jo from TX
Your argument is a bit flawed. In the first place, the date in A5 will always be the current day's date + 20 weeks. That means that this date wil constantly change. You would therefore have to introduce a start date in a row or column, and base your calculations on that. Also, you want exactly the same date, whether the cells both contain an N, whether either cell contains an N. Even when both contain a Y, you say you want to freeze the previous date (which is 20 weeks after the start date[or today's date using your criteria]). This means that you only need to calculate an estimated end date which is 20 weeks after the start date? "Jo from TX" wrote: 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
watch the wrapping by the newsgroups ... the word "THEN" should be one same
line in your code as the line If Application.WorksheetFunction.CountIf(Range("A2:A3 "), "Y") = 2 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JulieD" wrote in message ... 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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date formula | Excel Worksheet Functions | |||
addition to my date formula...required | Excel Worksheet Functions | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
Formula with date constraints | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |