此自定义验证无效，但我不知道发生了什么 [英] this custom validation is not working but I dont know whats happenning

问题描述

我知道excel编程"在其他程序员中并不很流行，但是我一直在努力做到这一点，而管理却在我的脖子上.

I know excel "programming" is not very popular among fellow programmers, however I've been struggling to get this right and management is on my neck..

我在excel上具有自定义验证:

I have the custom validation on excel :

=AND(LEN(AV15)=10,((VALUE(LEFT(AV15,2)))<=31),NOT(ISERROR(VALUE(LEFT(AV15,2)))),MID(AV15,3,1)="/",((VALUE(MID(AV15,4,2)))<=12),NOT(ISERROR(VALUE(MID(AV15,4,2)))),MID(AV15,6,1)="/",((VALUE(RIGHT(AV15,4)))<=2100),NOT(ISERROR(VALUE(RIGHT(AV15,4)))))

上面的验证应该接受以下格式的任何有效日期:

The validation above is supposed to accept any valid date in the format:

dd/mm/yyyy

它似乎在部分工作，但是不知何故，它接受的天数不能低于"12"，例如:

It seems to be working partially, but somehow it wont accept a day lower than "12", example:

 14/12/2010 -->accepted
 13/10/2010 -->accepted
 25/10/2010 -->accepted
 12/10/2010 -->gives error

乍一看，可能是((VALUE(MID(AV15,4,2)))<=12)导致此行为的原因，但我将其更改为31，但仍然出现错误，我需要进行验证才能接受输入:

At first glance one would thing that the ((VALUE(MID(AV15,4,2)))<=12) is causing this behavior, but I changed it to 31 and I still get the error, I need the validation to admit inputs in :

 nn/nn/nnnn

其中"n"是数字，我不在乎他们是否输入99/99/9999，我以后可以在vba代码上检查一下，但输入必须具体包含10个字符.

where "n" is a number, i don't care if they input 99/99/9999 I can check that later on vba code, but the input has to specifically have the 10 characters.

任何帮助将不胜感激

推荐答案

我放了您的公式，当您在日期前放置'时，它就可以了.发生的情况是，当您在13以下放置任何值作为日期时，它会像date那样评估日期，该日期在Excel中存储为number.当您在13以上时，它将被评估为string.因此，您需要做的是将单元格格式化为Text格式.然后就可以了.

I put your formula in and when you put a ' before the date it works just fine. What is going on is that when you put any value below 13 as the day it evaluates the date like a date, which is stored as a number in Excel. When you have above 13 it evaluates as a string. So what you need to do is format the cell to Text format. Then it should work just fine.

这篇关于此自定义验证无效，但我不知道发生了什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！