Chronological Date Orders

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 31 Mar 2008 10:24 GMT

Hi

I have a worksheet column (L6:L34) into which users are asked to enter dates
that payments are made.

The columns are formatted as 'Date' and Data Validation is set to ensure a
date is entered. A blank entry is allowed.

Initially all the cells are blank and the user is expected to populate the
cells, starting with the earliest date in L6 and moving sequentially down
the column. Of course, there's no guarantee it will happen that way.

It doesn't matter if a cell is skipped (left blank), but I would like to
ensure that any dates that are entered, are entered in chronological order,
with the earliest date at the top.

I guess a simple message alert in a cell outside the print area would be
sufficient in the event someone gets the date order wrong.

Can anyone provide me with a formula that would look at the column entries
and trigger the alert if the dates that have been entered are out of
chronological order?

The formula would have to ignore blank cells (including when the entire
column is blank), and a single cell entry (when only one cell has a date
entered).

TIA

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

MartinW - 31 Mar 2008 11:16 GMT

Hi Mike,

Maybe a Conditional Format.
Select L6:L34
And apply CF with
Formula is: =AND(L6<>"",L6<MAX($L$6:L6))

HTH
Martin

> Hi
>
[quoted text clipped - 24 lines]
>
> TIA

Reply to this Message

mlv - 31 Mar 2008 15:52 GMT

Martin Wrote:

> Maybe a Conditional Format.
> Select L6:L34
> And apply CF with
> Formula is: =AND(L6<>"",L6<MAX($L$6:L6))

Hi Martin

I had considered using conditional formatting, but couldn't see how I could
use CF to bring up the alert message.

With CF I could highlight the non-chronological cell entry by changing the
font and/or background colour, but I can't see how to convey to the user
what the problem is. Even if I could use CF to change the text in the cell,
the cell isn't large enough to carry the alert message.

Maybe I have to use a helper column to check each L6:L34 entry, and then use
the helper column to initiate the alert message if a date is entered that is
not in chronological order.

Something like:

=IF(AND(L6<>"",L6<MAX($L$6:L6)),1,"") Copied through (say) cells Z6:Z34

Then, in the message alert cell:

=IF(SUM(Z6:Z34)>0,"ERROR - Dates are not in chronological order","")

It's rather a convoluted approach, and I haven't tested the formula yet to
prove it works, but the principle is there, hopefully.

Maybe there is a shorter route?

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Rate this thread: