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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

UPN

Board Regular
Joined
May 14, 2006
Messages
138
problem also is dates are enter as person takes vacation, so some cells will have to be blank till data entered in them
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
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
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
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.
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138
will try

will try wht you gave me

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

Forum statistics

Threads
1,136,612
Messages
5,676,800
Members
419,651
Latest member
alexanderguhr

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
Top