formula for filtering and a defaulting date

S

Sue

Excel 2003
I have been struggling with a formula for the following and am getting
desperate:
1) Need a formula that will change a date to "1/04/05" when it is before
that date but must be able to leave any date alone that is after 1/04/05.
2) In a separate column I need another formula that will change a date to
"31/03/06" if the date is after that but will leave alone any date that is
before 31/03/06.
I have two formula's for the 1) scenario but I need them to work together to
achieve the result I need for that scenario. The date value comes from a
paste link cell.
=IF(C2>1/04/2005,1/04/2005,"1/04/05")
=IF(C2>1/04/2005,C2,C2)
Would these work if joined together and if so how?
Pleasssseee Help!
bj has helped but I think I take too long to reply back due to time zone.
Sue
 
A

Aladin Akyurek

=IF(N(C2)>0,IF(C2>=38443,C2,38443),"")

regarding 1-Apr-05.
Excel 2003
I have been struggling with a formula for the following and am getting
desperate:
1) Need a formula that will change a date to "1/04/05" when it is before
that date but must be able to leave any date alone that is after 1/04/05.
2) In a separate column I need another formula that will change a date to
"31/03/06" if the date is after that but will leave alone any date that is
before 31/03/06.
I have two formula's for the 1) scenario but I need them to work together to
achieve the result I need for that scenario. The date value comes from a
paste link cell.
=IF(C2>1/04/2005,1/04/2005,"1/04/05")
=IF(C2>1/04/2005,C2,C2)
Would these work if joined together and if so how?
Pleasssseee Help!
bj has helped but I think I take too long to reply back due to time zone.
Sue
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
S

Sue

Hi Aladin,
I have just tried your formula and it lets the date of 1/02/05 default to
1/04/05 but the date of 30/06/05 also defaults to 1/04/05 and I don't want it
to do that. I want it to let the 30/06/05 through as it is.
Can you help.
Thanks sue

Aladin Akyurek said:
=IF(N(C2)>0,IF(C2>=38443,C2,38443),"")

regarding 1-Apr-05.
Excel 2003
I have been struggling with a formula for the following and am getting
desperate:
1) Need a formula that will change a date to "1/04/05" when it is before
that date but must be able to leave any date alone that is after 1/04/05.
2) In a separate column I need another formula that will change a date to
"31/03/06" if the date is after that but will leave alone any date that is
before 31/03/06.
I have two formula's for the 1) scenario but I need them to work together to
achieve the result I need for that scenario. The date value comes from a
paste link cell.
=IF(C2>1/04/2005,1/04/2005,"1/04/05")
=IF(C2>1/04/2005,C2,C2)
Would these work if joined together and if so how?
Pleasssseee Help!
bj has helped but I think I take too long to reply back due to time zone.
Sue
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Click to expand...
 
S

Sue

Hi Aladin,
The formula works! I must have entered it wrong but it is going great now
thanks so much. Very grateful to you
Thanks
Sue

Aladin Akyurek said:
=IF(N(C2)>0,IF(C2>=38443,C2,38443),"")

regarding 1-Apr-05.
Excel 2003
I have been struggling with a formula for the following and am getting
desperate:
1) Need a formula that will change a date to "1/04/05" when it is before
that date but must be able to leave any date alone that is after 1/04/05.
2) In a separate column I need another formula that will change a date to
"31/03/06" if the date is after that but will leave alone any date that is
before 31/03/06.
I have two formula's for the 1) scenario but I need them to work together to
achieve the result I need for that scenario. The date value comes from a
paste link cell.
=IF(C2>1/04/2005,1/04/2005,"1/04/05")
=IF(C2>1/04/2005,C2,C2)
Would these work if joined together and if so how?
Pleasssseee Help!
bj has helped but I think I take too long to reply back due to time zone.
Sue
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Click to expand...
 
Top