Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Check out Chip Pearson's code at:
    http://www.cpearson.com/excel/DateTimeEntry.htm

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)) will result in a date value.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =Date(Mod(A2, 10000),TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100))

    Copy down

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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")))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •