data validation

UPN

Board Regular
Joined
May 14, 2006
Messages
138
I need to data validate a column. I want to make sure it has dates in. but there is no set dates to check

I have tested sheet with no date in field and it does calculations, which i dont want it to do

what I am looking for I guess is to see if cell contents are in mm/dd/yy format

Possible?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
problem also is dates are enter as person takes vacation, so some cells will have to be blank till data entered in them
 
Upvote 0
I need to data validate a column. I want to make sure it has dates in. but there is no set dates to check

I have tested sheet with no date in field and it does calculations, which i dont want it to do

what I am looking for I guess is to see if cell contents are in mm/dd/yy format

Possible?
See if this is what you want.

1. Select the column
2. Data|Validation...|Settings|Allow: Date|Data: greater than|Start date: enter a date earlier than any likely to be entered (eg 01/01/1900)|OK
3. Also format the column as date with the mm/dd/yy format
 
Upvote 0
no good

it didnt work... the cell i am trying to validate, I forgot the cell value comes from another cell

there is a formula in that cell as well the one i want to validate
 
Upvote 0
Re: no good

it didnt work... the cell i am trying to validate, I forgot the cell value comes from another cell

there is a formula in that cell as well the one i want to validate
This is not clear to me:
'the cell i am trying to validate' Which cell (eg D4)?
'I forgot the cell value..' Which cell?
'comes from another cell' Which cell?
'there is a formula in that cell as well' Which cell?
 
Upvote 0
try this

Forget about the above stuff lets try this one
a13 i put a date in
b13 put how many hour used
c13 is either a "P" do "V"

the sheet then decides where the above mentioned data is put
put if you forget to put a date in a13 it will put 1/00/1990 in the forgot line of data, but does put the hours and the Vor P

guess i what i really want is if there is any info in b13 or c13
a13 needs to have a date in it

this will also go down to a96 thru c96
 
Upvote 0
UPN

Try this then.
1. Select B13:C96
2. Data|Validation...|Settings|Allow: Custom|Formula: =$A13>0|Remove tick from 'Ignore blank'|Error Alert|Error message: put some message about having to enter a date in column A|OK

This should not allow any entry in columns B or C until column A has been entered. Column A could have its own Data Validation forcing a date entry as described in my previous post.

Another option might be to think about the formula (I assume a formula is being used) that places the data elsewhere. It might be possible to change this formula so that nothing was entered if column A did not contain data. If you want to investigate this further, you would need to tell us more about how the data is being moved and what formula is being used.

Posting a sample using Colo's HTML maker would make it easier for us to see what is going on.
 
Upvote 0
will try

will try wht you gave me

I am using excel 97 and i dont think that colo hmtl maker will work
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top