Data Validation with date format dd/mm/yyyy hh:mm and N/A

[bbaseballtheory]

I can't find an example anywhere of the following and was hoping somebody could help solve this for me?

I have a column (X3 is the first cell for entry) which I require the user to comply with entering a date time in the format dd/mm/yyyy hh:mm


I also want to allow them a text option so they can enter N/A or similar if no date is required. I need this to distinguish from blanks so I can ascertain what hasn't been filled in yet.

I've looked at an OR statement including a MOD trick I found to validate the date time, but including any reference to to the "N/A" is causing it to Red X upon entry.....

So in the custom data validation formula i've tried

=OR(MOD(X3,2),X3="N/A")


Where am I going wrong?

[6StringJazzer]

I don't understand how your MOD trick works. When I use it by itself it does not require that you enter a date. I can enter any number at all except an even number.

The reason you get an error is if you enter text in the cell, then evaluate MOD against the text, it causes the formula to result in a #VALUE error.

I would suggest this. This assumes that the date must be on or after Jan 1 2020. You can change to suit.

Please Login or Register  to view this content.

[bbaseballtheory]

Thanks for the solution. I picked up the MOD part of the formula from another forum where the validation solution required was to ensure that the full dd/mm/yyyy hh:mm was entered.

Although I don't 100% understand why it works I believe the idea was to make sure a number with a decimal was entered which would then translate into the time portion of the time date integer.decimal

I'm really looking to make sure the user inputs a full date time value instead of just a date. With the added option of noting an N/A if required

[6StringJazzer]

I think you may have copied the MOD solution incorrectly. For what you described, you want this:

MOD(X3,1)

That expression returns 0 if the number is an integer, which will be interpreted as FALSE. A date without a time is an integer.



Then the total solution is:

Please Login or Register  to view this content.

The reason I added the check for the date is that with your original solution you could also enter 1.1, which is in the year 1900 and may not be a valid date for you. If you really don't care what the date is you can remove that part of the formula. In that case it is no longer necessary to check for ISNUMBER since IFERROR will cover that for the MOD function.

Please Login or Register  to view this content.

[bbaseballtheory]

This makes a lot more sense deconstructed and explained.

Thanks for taking the time to get back to me. I will implement tomorrow and see if that fits the purpose. On the face of it, it looks perfect.