Custom Date Formatting

[medberg]

Hello,
So I need to validate that a certain column has a particular date format, and if it is not the right format the value is not accepted. Now I know how to do this with most formats, but for some reason this particular format is giving me trouble and an error message when I try to enter it. The format I need to use is described as this:

Format = YYYY/MM/DD hh24:mi:ss

I searched everywhere online and could find nothing of this sort, any help would be appreciated. Thanks!

 

[Jonmo1]

Try

YYYY/MM/DD hh:mm:ss

 

[medberg]

Hey,
Thanks for the quick response, I guess I should have stated my question more clearly though. Where do I even go to enter this? I changed the type of the cells to "custom" and entered what you suggested. It did not enforce the format though. Under data validation I don't know how to enter this format. Thank you.

 

[Jonmo1]

You put it in the right place - Format cells - Number - Custom.
It does NOT go in Data Validation.
If the new format was not displayed after doing that, it means your date/time value is not a real excel date/time.
It's just a text string that looks like a date and time.

Can you post an example of the value you have in the Cell where you are applying that format?

 

[medberg]

Oh I see, I must have misunderstood how that works. For example, if I type 2011/12/12 13:12:12 with your suggested format, it keeps it, but displays 2011/12/12 1:12:12 PM in the formula bar. What I need, and I hope this is possible, is if someone types 12/12/12 12:12:12 for example it will get rejected, because the year is of the format yy and not yyyy. Or maybe if they add a character that shouldn't be in there, like PM or AM (it should be military time, so PM or AM are not needed). Thanks once again for the really fast reply.

 

[Jonmo1]

You would use BOTH Cell formatting AND Data Valication together for this..

Format the cell as you wish from above YYYY/MM/DD hh:mm:ss
AND
In Data - Validation, choose Date
And use Between any dates of your choosing.

Now, the user will only be allowed to enter a Valid date (via data validation)
AND
It will be formatted as you wish through the Cell Formatting.

It won't matter what format the user types the date with, as long as excel recognizes that it is a date.
The formatting will then convert it to the format you chose in the cell formatting.

 

[medberg]

Awesome thanks a lot. I didn't realize they would work together in that way. It's exactly what I need!

 

[Jonmo1]

Glad to help, thanks for the feedback.