Date Range Question

[Rodman]

Assume there is a date range from 1/1 to 3/31 (i.e., first quarter).

In cell A2 I have a start date, in cell B2 I have an end date.

If the start date is greater than the end date
or the end date is less than the start date
or the dates are not within the date range
display "Date Error" in cell C3

Can someone help with a macro or formula for this problem?

Thanks in Advance

 

[Jim Cone]

Dates in A2:A91
Start Date in B2
End Date in C2
Message formula in D2
Min function for dates in E2
Max function for dates in F2

Formula in D2...
=IF(OR(B2< E2,B2>F2,B2>C2),"Start Date Error",IF(OR(C2<E2,C2>F2,C2<B2),"End Date Error","OK"))
--
Jim Cone
Portland, Oregon USA



"Rodman" <[email protected]>
wrote in message
Assume there is a date range from 1/1 to 3/31 (i.e., first quarter).
In cell A2 I have a start date, in cell B2 I have an end date.

If the start date is greater than the end date
or the end date is less than the start date
or the dates are not within the date range
display "Date Error" in cell C3

Can someone help with a macro or formula for this problem?
Thanks in Advance

 

[smartin]

Assume there is a date range from 1/1 to 3/31 (i.e., first quarter).

In cell A2 I have a start date, in cell B2 I have an end date.

If the start date is greater than the end date
or the end date is less than the start date
or the dates are not within the date range
display "Date Error" in cell C3

Can someone help with a macro or formula for this problem?

Thanks in Advance

So, you want to ensure both A2 and B2 are in the quarter?

=IF(OR(A2<DATE(2009,1,1),A2>DATE(2009,3,31),B2<DATE(2009,1,1),B2>DATE(2009,3,31)),"Date
Error","No Date Error")

 

[Rodman]

Thanks! - This solution worked great!

now I have been asked to put the error message in the offending cell.

ex if cell A2 is outside of the date range the error message placed in cell
A2 coule be "#DER (for date error).

Thanks In Advance

 

[smartin]

You do see where to put "#DER" in the formula I gave, yes?

=IF(OR(A2<DATE(2009,1,1),A2>DATE(2009,3,31),B2<DATE(2009,1,1),B2>DATE(2009,3,31)),"Rodman's
Message Here","No Date Error")

Or did you mean something different?

 

[Rodman]

Not Exactly...

In the sample being discussed if there was an error (i.e., the data not
within the range) an error would be displayed in cell C2. I would like to
put the error in the cell in which the error occurs. If, for example, the
date in cell A2 is not within the date range I would like to put the error
message #DER in that cell.

Additionally, I would like to make sure the start date is not greater than
the end date and the end date is not less than the start date (for that date
range). I think this will have to be a macro.

I have sucessfully used your example to make sure the dates are within thr
range but have not figured out how to determine the greater- than, less-than
date issue.

Regards,

 

[smartin]

Ah, sorry.

Well you can't replace the cell values (A2 & B2) with an error message
(well you could with VBA, but I think this is overkill). What you can do
easily is apply conditional formatting to the problem cells,
highlighting error values when they occur.

This will be easiest if you have the date limits (e.g., 1/1/2009 and
3/31/2009) is cells somewhere. Let's assume they are in Y1 and Z1,
respectively.

In A2 you want to check three things: A2<Y1, A2>Z1 or A2>B2 should all
throw an error. In A2, the conditional format expression would look like

Formula is: =OR(A2<Y1,A2>Z1,A2>B2)

Make the conditional format something vivid, like bold red font against
yellow shading.

The conditional format expression in B2 would be quite similar.

Hope this helps!