Entering dates without typing slashes

Topic

New Reply

I know I have done this in the past but when I format a cell with the date format I want and then try typing the date without the slashes, Excel changes the date to a different date. Is there something else I have to do to be able to enter the date without slashes?

Reply | Quote

Replies

Check out Chip Pearson’s code at:http://www.cpearson.com/excel/DateTimeEntry.htm

Steve

Reply | Quote

Are you opposed to dashes as well? If not, you can enter the date as 1-27-10 or 1-27 if you want the current year.

Reply | Quote

I don’t have an answer, but it seems to me that what you asking for is an Excel equivalent to an Access Input Mask.
My understanding is that Excel does NOT provide input masks, so you need a VBA solution like the one offered earlier in this thread.

Reply | Quote

Highlight and Right click the column you want to enter the dates.
Select Format Cells.
On the number tab choose custom and enter in the Type Box 00/00/0000.
It looks like a V between the 0s but they are forward and backward slashes. If you want to use the date values in calculations, you will need to add a column containing formulas that will translate them into actual dates for Excel.If you want that formula, let me know.

Reply | Quote

Thanks for everyones help. I think the custom format should work just fine. I would also be interested in the calculated expression that would change this to an actual date.

Reply | Quote

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) will result in a date value.

Reply | Quote

=Date(Mod(A2, 10000),TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100))

Copy down

Reply | Quote

Should have thought of these – they use Linda’s formatting and turn the results into text then to “dates”. Both would need to be formatted to a Date field if the result are intended to be dates – as is, they return numbers.

=–(TEXT(H7,”00/00/0000″))

or

=DATEVALUE((TEXT(H7,”00/00/0000″)))

Reply | Quote