I want to use Data Validation to only allow the date format of the below for a range of rows and columns.
mm/dd/yyyy hh:mm
I found the below formula online to allow for the date only (I think), but I would like to understand how this works/what it is doing in order to manipulate it to do what I need it to!
=AND(ISNUMBER(B2),LEFT(CELL("format",B2),1)="D")
Thanks
This formula checks 2 things.
First that value entered in B2 is number. Since, Excel stores all datetime value as double (i.e. numeric).
Second, it checks Cell's format. Most date formats returns D4/D5.
So the formula is checking that Left most character returned from CELL("format",B2) = D.
However, do note some date formats return "G" instead of D4/D5 (ex. 2-Jan-17, "[$-en-US]d-mmm-yy;@" format).
Unfortunately this does not check if there is time value along with date value in a cell.
I can't think of way to ensure time portion is entered in a cell containing datetime value.
One workaround is to split entry for date portion and time portion into 2 separate cells and validate each.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
It's saying
Look at the cell B2 and identify its format.
If the first character of that format type is D, that is the format of B2 is a date
and B2 is a number then return TRUE.
I'm thinking this overkill, since if a cell is a date it must therefore be a number, so
I would say you just need
=(LEFT(CELL("format",B2),1)="D")
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
A cell can be formatted using a date format, but still contain text, so the check for number is necessary.Originally Posted by Special-K
The values returned by CELL for "format" are as follows, none of which is exactly what you are looking for.
m/d/yy or m/d/yy h:mm or mm/dd/yy
"D4"
d-mmm-yy or dd-mmm-yy
"D1"
d-mmm or dd-mmm
"D2"
mmm-yy
"D3"
mm/dd
"D5"
h:mm AM/PM
"D7"
h:mm:ss AM/PM
"D6"
h:mm
"D9"
h:mm:ss
"D8"
Be aware that this just applies to how data is displayed, and tells you nothing about the underlying data. Can you describe more fully the reason that you want this validation?
My sheet is used to record specific dates and times of barges being loaded then emptied. I just want to make sure that the only thing that is entered into the cells are the specified dates and times in the m/d/yyyy h:mm format.
For dates and times, the way the user enters it is not necessarily related to how the cell is formatted. This is confusing for many new Excel users. The formatting is just for display purposes. I could type in any of the following
Jul-14 9:00 AM
7/14 09:00
1/14/2017 9 AM
42749.375
and if the cell format is m/d/yyyy h:mm they would all look the same:
7/14/2017 9:00
So you are basically saying that you want to require the user to enter a valid date and time. You should set the formatting for those cells in advance for how you want them to look. Then your initial formula in your first post will ensure that the user enters a valid date.
Be aware that rule will not prevent someone from entering a date that doesn't makes sense for your application, like 4/5/1952, or 2/7/2092. If you want to restrict what dates make sense, you can expand your original formula.
We have all mentioned the CELL function, which nobody really explained. Maybe you'd like to know what that is. The CELL function will return various bits of information about a cell. The first argument is a string saying what information you want, and the second argument is the cell. If the string is "format", it will return information about how the cell is formatted. I showed in my post above what it will return for various types of date formatting. (There are several other options besides "format" which you can look up in Excel Help.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks