Excel - Mulitple Validation Dates

[jsomer2000]

I would like some help on multiple validation on dates - Example below

If I want a user to enter a date in Cell A1 and I want to validate the date based on the following rules:

1. Date Format DD/MM/YYYY
2. You can't enter a future date
3. You can only enter weekdays
4. You can only enter a date in the current month or previous month

I have tried several times but I get an error message when I use validation - formula I used =AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7,A1>= DATE(YEAR(A1),MONTH(A1)-1,1),A1< DATE(YEAR(A1),MONTH(A1)+1,1))

[SteveG]

Hi,

For data validation you could use this where the date is entered in A2 by the user. You'd have to format the cell to be dd/mm/yyyy though. I don't think there is a way to "force" someone to enter the date in that format. When they enter the date since the cell is formatted that way it should be obvious and if they try to change the format the validation alert will prompt them they can't. This wouldn't be 100% reliable but it gets you part way there.

=AND(CELL("format",A2)="D1",A2<=TODAY(),OR(WEEKDAY(A2,1)<>1,WEEKDAY(A2,1)<>7),AND(DATE(YEAR(A2),MONTH(A2),DAY(A2))<=TODAY(),A2>=DATE(YEAR(A2),MONTH(A2)-1,1)))

HTH
Steve

[Haseeb Avarakkan]

Also try,

=AND(WEEKDAY(A1,2)<6,A1<=TODAY(),A1>=EOMONTH(TODAY(),-2)+1)