0

I have dates in E2 and F2 cells of an excel.The dates are in MM/DD/YYYY fromat.I want to apply validation in such a manner that date in F2 must be greater then dates in E2 . In case of failure we should display an error. I am using Microsoft Office 2007.

Help would be appreciated.

palak mehta
  • 696
  • 4
  • 13
  • 30

2 Answers2

1

You could use conditional formatting? In your case the background of one of the dates could turn red if the date is too low compared to another.

Quick google: http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html

bjornruysen
  • 850
  • 1
  • 6
  • 15
0

Should be quite easy doing =IF(F2>E2;"SUCCESS";"ERROR")

You can omit the "SUCCESS" and you only display the error should it fail. This will work as long as the fields are actually formatted as a proper date.

Updated code:

=IF(AND(NUMBERBLANK(E2:F2)=0;F2<E2);"ERROR";"")

What this does is: Check if both cells are filled, if they are chech if E is smaller than F, and write ERROR if that is the case.

To do this for the entire column you need to replicate the formula. For existing dates, just double click the lower right knob in the cell the formula is, and it SHOULD replicate down till the end of the row (if the row is next to the date). Other than that, just pull the knob down as far as you need it.

Andreas
  • 227
  • 5
  • 11
  • Thanks Andreas.Actually , I am new to excel validations.Hope you wont mind telling , where and how should I write this formula on Excel. – palak mehta May 14 '12 at 13:16
  • FYI.I am storing date as MM/DD/YYYY – palak mehta May 14 '12 at 13:16
  • Just to make sure. Validation for you just means that You have your sheet, let's say two rows with either Date1 and Date2 formatted however you want it to, as long as it is a date. All you need to do is DISPLAY an error should the Validation (Date1 < Date2) fail. In that case, just pick the cell you want to display the error at and insert it there. If not, specifiy your problem more closely – Andreas May 14 '12 at 13:20
  • Say, I have two columns: FromDate and ToDate which are in cells E and F respectively. After I fill ToDate field and press the tab button , the dates of E and F cells are compared(in my case FromDate and ToDate) .If value in cell F is smaller then value in Cell E , then an alert(coloring of cell) should get displayed. Moreover , this validation should work for every adjacent column of cell E and F. Hope I am sounding clear. Thanks – palak mehta May 14 '12 at 13:27
  • Hi Andreas , I am putting the same formula in cell G2 , and entering the 4 in E2 and 2 in F2(For testing purpose).But it's not working .i.e. no error message is coming up. Could you please check it at your end.I am using MSOffice 2007. Regards, – palak mehta May 14 '12 at 14:03
  • Hi Andreas, I just found the solution. I made some changes , in the formula you have given. =IF(F2>E2,"SUCCESS","ERROR") Thanks for your help. Regards, – palak mehta May 14 '12 at 14:14