Formula Hell, ...OK, maybe just Formula Heck.

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
Hi Gang,

This part of my formula work like it should;
=IF(C3>37346,"invalid date","")

I cant figure out how to make the second part work "<37527"

What I'm looking for is =IF(C3>3734OR<37527,"invalid date","")

I cant seem to make it go. Any ideas?

Thanks for your time,

Jim
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Flenley

Board Regular
Joined
Jul 31, 2002
Messages
71
This should work.

Just adjust the numbers


=IF(AND(C3>1,C3<4),"invalid date","")
 
L

Legacy 98055

Guest
You almost had it!

=IF(OR(C3>37346,C3<37527),"Invalid Date","")

:)
This message was edited by TsTom on 2002-08-29 00:00
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
On 2002-08-28 23:58, TsTom wrote:
You almost had it!

=IF(OR(C3>37346,C3<37527),"Invalid Date","")

:)
This message was edited by TsTom on 2002-08-29 00:00

Won't all dates be either greater than 37346 or less than 37527, in which case they will all be invalid? I think the OP meant if the date falls between the dates, it is invalid, in which case AND would be the correct usage. Please correct me if I'm wrong.

Richard
 

jeh

Active Member
Joined
Apr 27, 2002
Messages
250

ADVERTISEMENT

Thanks Everyone,

I guess I should explain my train wreck a little better. If the dates fall between the two numbers, then the date is valid, if its greater or less than the two numbers, it's invalid.

Thanks
 
L

Legacy 98055

Guest
Flip the comparison operators:

=IF(OR(C3<37346,C3>37527),"Invalid Date","")
If less than 3/31/2002 OR greater than 9/28/2002...

Most date searches include the beginning and ending dates. Depends on what the OP wants?
Probably:
=IF(AND(C3>=37346,C3<=37527),"Invalid Date","")

Tom
 

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151

ADVERTISEMENT

All you have to do rearrange the code Flenley gave you so it read like this.

<pre>
=IF(AND(C3>37346,C3<37527),"valid","invalid date")
</pre>
 
L

Legacy 98055

Guest
I was wrong.
Looking at the Opost now, I'm not too sure exactly what they want?
Tom

I work nights and am off tonight. :)
This message was edited by TsTom on 2002-08-29 00:27
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Then swap your signs around in Toms,

=IF(OR(C3<37346,C3>37524),"Invalid Date","")

If date is between March 31 and September 28 inclusive, it returns nothing, otherwise Invalid Date.

Richard
 

Forum statistics

Threads
1,148,048
Messages
5,744,501
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top