Date Validation - Excel 2003

[Guest]

Hi,

The columns B and G in a spreadsheet are formatted to except date values in
the form: dd/mm/yyyy.

I need to set validation on Column G to only except a date that is either
equal to or greater than the date in Column B.

Any help or advice would be greatly appreciated.

Regards,

Simon.

 

[Gord Dibben]

Select column G and Data Validation>Allow>Custom

=$G1>=$B1


Gord Dibben MS Excel MVP

 

[Guest]

two ways but not as you want.

What you are looking for is a way for a cell to reference itself and then
compare itself to another cell. As soon as you type in a date (so it can
reference it - it wipes out the formula that is used to reference itself)

So:
1) This formula returns a 0 if date is less then the date and the DATE if
it is => then listed "0" (zero) can be set to not show in the options
setting so the space is blank

=IF(B5>=G5,B5,"0")

2) This formula returns a NOT VALID DATE if date is less then the date and
the DATE if it is => then listed.

=IF(B5>=G5,B5,"NOT VALID DATE")

NOTE: These are NOTES placed next to the date you want to reference so that
the inputer sees a FLAG that the date is wrong and nothing if it is OK.

Danny

 

[Guest]

Many thanks this is greatly appreciated. Just one more thing... what effect
do the $ signs have in this formula? I am aware of absolute cell referencing
which uses the $ sign either side of the cell reference letter, but not where
a single one is used.

Many thanks,

Simon.

 

[Stephen]

Rather than "the $ sign either side of the cell reference letter", think of
the first $ sign as before the column letter and the second one as before
the row number. The first one makes the column absolute, whilst the second
makes the row absolute. Hence there are four variants:
A1 (both column and row relative)
$A1 (column absolute, row relative)
A$1 (column relative, row absolute)
$A$1 (both column and row absolute)

 

[Guest]

I have figured out your problem if you are looking at referencing "todays
date" - It returns an "OLD DATE" if older than "TODAY" or puts in the date if
equal to or newer then "TODAY"

=IF(TODAY()<=L35,L35,"OLD DATE")

If you just want a blank space just delete everything between quotes"

=IF(TODAY()<=L35,L35,"")