Excel Date validation multiple conditions or custom formula Help please

i43 · Mar 2, 2013

Excel Date validation multiple conditions custom formula Help please

(using Excel 2007 (win7)

Question : is this possible to do custom data validation for these USER ENTRY Fields (cells) A3:A19

1 I have User entry cells from A3->To->A19 (A3:A19)
2 I have User entry cell (D2) where user put Date for the period Start
< Data entry Field; any month, any year - but must be the 1st Day of the month
(custom data validation formula [ =DAY(D2)=1 ] will return 1st day of the month) this is OK
3 Period end (D20) is calculated field <-- [ =DATE(YEAR(D4);MONTH(D2)+1;0) ]
will get the last day for the month entered in cell(D2) this is solved

problem ! with specific data (DATE) validation
here is a list of conditions what must passed before the date entered is accepted in WB
1. must match the range of dates user specified period i.e. must fit into period selected in cell(D2)
2. (Must keep the timeline in ascending order i.e. from (A3) --> (A19)
----------
user entry column
01.08.2013 <-- Is OK
02.08.2013 <-- Is OK
05.08.2013 <-- Is OK
31.08.2013 <-- Is OK
for example if august.2013 was selected for period (01.08.2013 --> 31-08-2013 )
3. Allow duplicate, but keep them next to each other ( together ),
like timeline ascending order, (from user side ... only + 1x ROW below )
--------------------
user entry column
01.08.2013 <-- Is OK
02.08.2013 <-- Is OK
02.08.2013 <-- Is OK
05.08.2013 <-- Is OK
04.08.2013 <-- is NOT Valid (in this ROW) it was entered after 05.08.2013 <-- was entered above already
02.08.2013 <-- is NOT Valid
any text or ""; blank, empty <-- is NOT valid
07.07.2013 <-- is NOT Valid (outside 'period ' criteria
09.08.2012 <-- is NOT Valid (outside 'period ' criteria
30082013 <-- is NOT Valid entry (date) format
31.08.2013 <-- Is OK

any prompt or Msg box for wrong entry will do the job

Thank You in advance

I.

Andrew Poulsom · Mar 3, 2013

Select A3:A19 and try the custom data validation formula:

=AND(A3>=D$2;A3<=D$20;A3>=MAX(A$2:A2))

Note that it's not possible to invalidate blank cells.

i43 · Mar 3, 2013

Re: Excel Date validation multiple conditions or custom formula Help please (solved)

Andrew Poulsom said:
Select A3:A19 and try the custom data validation formula:

=AND(A3>=D$2;A3<=D$20;A3>=MAX(A$2:A2))

Note that it's not possible to invalidate blank cells.

Thank You ! Thank You ! Thank You Mr. Andrew Poulsom

Your offered formula works just PERFECT !
This formula eliminates now the main problem ( accidently entered wrong dates from user side .. )
This is what I was after

(those blank cells are not a big deal, because blank row will give wrong staring point to next (odometer) entry and result is a mess J )

Million thanks to You

Appreciate Your help and kindness

I.

Excel Date validation multiple conditions or custom formula Help please

i43

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

i43

New Member

Similar threads

Forum statistics

Share this page

Excel Date validation multiple conditions or custom formula Help please

i43

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

i43

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock