Two date validations

How do I create two validation rules for one cell containing a date? 

1. The date (Cell A1) must be between a certain range (12/01/2009 and 
12/31/2012). 

2. The date must not be greater than Cell A2. 

So far I got the first rule working, but I can't figure out how to do both. 
Any advice would be greatly appricaiated.

Thanks,
~Gabe
0
Utf
5/11/2010 11:32:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
985 Views

Similar Articles

[PageSpeed] 22

I'd try:

Select A1
Data|Validation (xl2003 menus)
formula is:
=AND(ISNUMBER(A1),A1>=DATE(2009,12,1),A1<=DATE(2012,12,31),A1<=A2)



Gabe wrote:

> How do I create two validation rules for one cell containing a date? 
> 
> 1. The date (Cell A1) must be between a certain range (12/01/2009 and 
> 12/31/2012). 
> 
> 2. The date must not be greater than Cell A2. 
> 
> So far I got the first rule working, but I can't figure out how to do both. 
> Any advice would be greatly appricaiated.
> 
> Thanks,
> ~Gabe

-- 

Dave Peterson
0
Dave
5/11/2010 11:59:45 PM
Data > Validation > Custon > FormulaIs:

=AND(A1>40148,A1<41274,A1<A2)
-- 
Gary''s Student - gsnu201002


"Gabe" wrote:

> How do I create two validation rules for one cell containing a date? 
> 
> 1. The date (Cell A1) must be between a certain range (12/01/2009 and 
> 12/31/2012). 
> 
> 2. The date must not be greater than Cell A2. 
> 
> So far I got the first rule working, but I can't figure out how to do both. 
> Any advice would be greatly appricaiated.
> 
> Thanks,
> ~Gabe
0
Utf
5/12/2010 12:13:01 AM
Reply:

Similar Artilces:

date taken for photos copied
date taken for photo copied, actually modified date on any documents copied in explorer tends to changed to the Now (date and time of copying). especially annoying for folders anyway to change this default behaviour? On 5/25/10, GS posted: > date taken for photo copied, actually modified date on any documents copied > in explorer tends to changed to the Now (date and time of copying). > especially annoying for folders > anyway to change this default behaviour? Use the value in the metadata (tags)? -- Gene E. Bloch (Stumbling Bloch) anyway to cha...

Two at once??
Hi I would like to be able to see the contents of 2, or even better 3, mail folders when I open Outlook rather than just the one at present. Ideally one above the other. Any ideas that could help me. Thanks Right click on a folder and choose "Open in a new window". Then right click on the taskbar & choose "Tile Windows Horizontally" "Zaphod" <zaphodbeeb@ntlworld.com> wrote in message news:hZFLe.8727$wh6.2887@newsfe2-win.ntli.net... > Hi > > I would like to be able to see the contents of 2, or even better 3, mail > folders when I ...

Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

Duplex printing two up
I am working on a program to print bills. I designed a report with two pages, the customer address on page one and the billing info on page two. I then print it auto duplex in landscape and it works correctly. We then fold it in half to be mailed like a postcard and the bill is hidden inside. Life is good, except for one small problem. I need to print them two-up, because our printer will only auto duplex a full page, and we do not want to waist half of every sheet of stock. We need two addresses on the front with two bills on the back. We will then cut them into two bills and fold a...

Two versions of "personal folders"
I seem to have two versions of "personal folders" in my Outlook 2003. Only one of them is used really, I think the other must have been created during a backup from file or something. Can I merge them together somehow? I tried to copy the unused one into the other, but it claims that special folders can't be moved. They each point to a different data file, the second one pointing to my backup. The weird thing is that as far as I can tell, everything from backup went into the "main" personal folder too; I had no files or folders at all before backup. Thanks for any help...

Date() Not Working in 2007
Anyone know why Date() does not work in 2007 Access and what has replaced it? Thanks! I don't have ac2007 but I suspect Date() works as it always has. Do you have any reference problems? http://www.accessmvp.com/djsteele/AccessReferenceErrors.html Does it throw an error? DSmith wrote: >Anyone know why Date() does not work in 2007 Access and what has replaced >it? Thanks! -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 ...

Date and Timestamp in Filename
I have a spreadsheet that is modified several times a day and by several people. Is it possible to somehow insert the date and timestamp (perhaps with a macro?) into the filename? It is very difficult to get users to use abbreviations/version numbers etc to show when the last modified spreadsheet was used - many "forget". If there was a way that automatically put the date and time into the filename then users will know which is the most recent document to use - and not have any excuses! many thanks peeps! Emily, Pretty easy ActiveWorkbook.SaveAs Filename:=sFilename &...

X axis collapsing to date on hourly data
Hello, I have a spreadsheet I am trying to chart that has values by date and time, IE Date-Time, Value 9/17/07 20:00, 1 9/17/07 21:00, 2 9/17/07 22:00, 4 9/17/07 23:00, 1 9/18/07 00:00, 2 9/18/07 01:00, 1 9/18/07 02:00, 0 When I highlight all of the above and select "Insert Chart" and choose line chart, it creates the chart as if it had the following information: Date-Time, Value 9/17/07, 8 9/18/07, 3 It also looks more like a bar chart then a line chart. Anyone have an idea on why this would happen and how to fix it? -Jason I am mistaken, it is collapsing into all 24 series...

Combining two worksheets with same case identifiers
Hello, I am trying to merge two workbooks containing data from an experiment. The first three columns have identical data in both workbooks, and include a participant number, value for Variable1,and value for Variable2. Each participant takes up about 12 rows of the worksheet, this includes all possible combinations of Variable1 and Variable2. The last three columns represent judge ratings. Half of these ratings are entered in Worksheet I, and half of them in Worksheet II. Some rows in Worksheet I contain missing data which is contained in Worksheet II. How do I go about combining these...

Cell Validation
I am working on a spreadsheet, one of the columns is to enter a product name. The products have to be specific as they are seleced from a VLookup table. I have used select from list on the validation, and specified the range to choose from, but everytime I click on the down arrow to select the product I am at the bottom of the list. Does anyone know how to set this so I could enter a letter ie. 'b' and go to the b's on the list of 't' to go to the t's on the list to save scrolling up and down. Any suggestions would be appreciated When you open the Data Val...

Printing Two Forms with one Command Button
Is there a way to allow someone to print record information from two different forms with one command button. Situation is I have a form that has basic information about a contact, user click on a command button to open a new form if they need additional "detailed" information about this contact. What I would like to do is on the second form is have users press a print button that would allow them to print both forms. At this time the user needs to go to each form and press print. Both forms are set to stay open. -- Message posted via AccessMonster.com http://www.accessmonster.c...

Contacts' Modified Dates Change After Opening
Hello This problem popped up 3 days ago, I don't know what triggered it. After I double click on a contact, to open it, the modified date for this contact is updated to the present date and time, although I did not modify the contact. Right after I open it, its modified date is altered. I want to avoid this as I often use a "sort by modified date" view. Please help me solve this problem Thanks Gz If you open something in OL the modified date is changed, its default/std behaviour. "grendi" <ufoREMOVErobotTHISgrendizerusTO@CONTACTyahoo.MEcom> wrote in messag...

Compare two files
I have two spreadsheets that I need to compare and then combine. The first file includes all employees including file number, name and address. The second file is for active employees only. It includes only file number and name. I want to add the second file to the first so I can eliminate terminated employees. Is there a way to do this See Chip's site www.cpearson.com He has lots of stuff on comapring sheets. Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Peter" <anonymous@discussions.microsoft.com> wrote in message news:...

How do you make a two page purchase order?
I am using a Purchase order template, and the order needs to be 2-3 pages long. How do I continue the page on to another page and have the ending total be correct? If the sheet formulas are working correctly, then just put page breaks at the spots you want the new pages to begin. -- Gary''s Student - gsnu200713 ...

Functionality Question: Can you have two views of a folder?
We're looking a building a project/knowledge management system & are considering the options within outlook. Ideally, I'd rather have a single calendar (or other folder) which has a view of certain projects for me; other projects for my co-workers, and sub-set of both for our boss. Obviously we'll need to be *able* to see the suppressed items, but normally wont. While reading "Outlook 2000: In a nutshell" by Syroid & Leuf (O'Reilly), I'm wondering if we could have a master calendar folder, and have other folders with filters applied that show a subset o...

Date in forms
I would like to have a command button that I could use to insert a date in a date field. the date would be for scheduling events at the beginning of each week, hence I would like the dates to be for the beginning of each week. First date could be 04-mar-07 the next record in the date column would be 11-mar-07. Each record would have the beginning of the week date. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 ...

two users, one computer, shared folders??
I am trying to set up Outlook so that two different users can access the same Calendar, Tasks, Contacts, etc. Has anyone done this successfully? So far I have two users with two independent sets of folders, but I want the same Calendar etc. to show up for both users. thanks! What operating system and version of Outlook are you using? Do you use windows profiles or only Outlook profiles? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Sue G <anonymous@discussions.microsoft.com&g...

Allowing only Date and Integer for user form
I have a user form and as part of the procedure when I click OK is the following .... 'Make sure a number is entered If TextName1 = "" Then MsgBox "You must enter a number." Exit Sub End If 'Transfer the number If Range("M5") = True Then Range("O5") = TextName1 If TextName2 = "" Then MsgBox "You must enter a date." Exit Sub End If 'Transfer the date If Range("M5") = True Then Range("P5") = TextName2 1. I would like to make sure that only an integer is...

Adress Book Sync over two Sites
We have a one Ex2k3 at our site with a own AD site1.com and we have still a tust to an other AD site2.se how can i sync the adressbooks (like a Siteconnector at Ex5.5) MIIS - Identity Integration Services. "HWU" <HWU@discussions.microsoft.com> wrote in message news:CA18AFD6-70F8-444B-B04E-B1D6A99DB547@microsoft.com... > We have a one Ex2k3 at our site with a own AD site1.com and we have still > a > tust to an other AD site2.se > how can i sync the adressbooks (like a Siteconnector at Ex5.5) > > Take a look at Jim McBee's blog, he recently wrot...

Can I change a column of dates from USA to UK format?
Using Excel 2000. Imported data from TAS, an accounts package. The data is imported in csv format in Excel. The dates in the dd/mm/yy format have been recognised as UK, but the dates picked up as dd/mm/yyyy are all in USA format, i.e. mm/dd/yyyy, despite all being entered in TAS as dd/mm/yy. Without going through the whole column's transactions individually & changing the USA ones, is there a way to do this with all the USA dates? I don't use TAS, but if it creates a text file, you could do your import and specify the each field the way you want. If TAS dumps out a .csv file...

Macrs with Validation Rules
I have a spread sheet with a tab called Estimnates once the Validation Item for the estimated item is changed to "Contracted" I want it to Copy the Entire row to another Sheet called Contracted Projects. Is this possible? Would the best Option be to have it Copy all Items listed "Contracted" when the Workbook it closed or upon clicking a select button? I can not have it duplicate Items. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup a...

Excel.exe is not a valid Win32 Application.
My system: Vista Business 32bit with SP1, Office 2007 Professional with SP1. For a while I was running Vista (without SP1) and using Office 2007 with SP1. Rarely any issues. Sometime after installing Vista SP1, I've been having odd issues with my Office installation, Excel especially. I don't know if it's its SP1 related, or if it's just something went wrong with the Office install. Here's the issue. I leave my PC on 24/7 and simply lock it when I leave the office. After several days, Excel will just stop working, completely. When I try to launch excel I get t...

Exact Validation Rules for a combo boxes
Hi All, I want to validate to either alllow or deny a user's entry to certain combo boxes in the form. The comboxes have the lists from another table. So this is what I have in the BeforeUpdate if cboPlace <> "Away" or cboPlace <> "Home" or cboPlace <> "Dont Know" then me.undo cboPlace.SetFocus end if but that's not working. Please help. And other cbos are pulling from a table, which has a lot more data, how do i go about validating that without having to hardcode it? Thanks. I was able to use the validation rule for the tables,...

Merging two sheets
This is what I have > > Sheet 1 has two columns > Col1 ; Col2 > Job; program ( combination of Job + program is unique ) > ABC4DX; ABC123 > ABC4DX; ABC475 > AXC123X; AXC123 > ABC135x; ABC124 > > > > > > Sheet 2 (This is a super set, has many rows , some are matching to th > sheet 1 and some not , the job + program will have duplicates) > > Col1; Col2; Col3; Col4; Col5; Col6 > Job; program; DBB; insert; update; lock > ABC4DX; ABC123; PVM; I; ; L > ABC4DX; ABC123; PTM; I; ; L ...

Data validation failures
I have several templates set up that are called by a master sheet. These sheets contain information with Data validation drop down lists. Excel is continually having problems with these data validation cells and commonly this is in the form of either only 1 cell with data validation drop down list functioning, or it is interfering with pictures on screen or it is moving combo boxes around the screen. This is not in code to do this either, it is very confusing and I am tempted to say that the data validation option has been very badly programmed! Like I say these functions all work perfectly in...